Opening SQL Server 2008 Remotely
I have a Windows Server 2008 R2 network. On the server resides a vb.net executable application program and a SQL Server 2008 database. When I am logged directly onto the server I can launch the executable and open the database. Unfortuantely, Windows Server 2008 does not support Windows 7 style graphics (i.e., forms with rounded corners and banners with opacity). However, when I navigate from my desktop to the folder on the server with the executable and launch the executable I do get the Windows 7 style graphics, but I get the following error when I try to open the database with these statements.
SqlConnection0.ConnectionS
SqlConnection0.Open()
strDataServer = "BDP-Server/SQLEXPRESS
strDataBase = "BDP_Systems"
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
SQL is configured with the "Allow Remote Connections" box checked.
Any thoughts?
Thanks.
SqlConnection0.ConnectionS
SqlConnection0.Open()
strDataServer = "BDP-Server/SQLEXPRESS
strDataBase = "BDP_Systems"
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
SQL is configured with the "Allow Remote Connections" box checked.
Any thoughts?
Thanks.
DrewKjell
Are you able to connect to the database using Management Studio from your workstation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DrewKjell,
I can not connect from my workstation. The network or any of the mapped drives does not appear in SQL Server Management Studio when I try to attach a database. I only see my local drives.
CmdoProg2,
I can ping BDP-Server. I found a Microsoft article that describes how to open the SQL Server port. It has a "Microsoft Fix-It" link to set up the port. Should I use this? I haven't used one of these "Fix-It" links before and I want to be careful with our server.
Also, will I have to install SQL Server on every workstation that will access the Windows Sever 2008 application programs or will the Windows Server 2008 SQL Server provide the access? I'm not sure I want to load SQL Server onto every workstation (around 20 of them).
Thanks.
I can not connect from my workstation. The network or any of the mapped drives does not appear in SQL Server Management Studio when I try to attach a database. I only see my local drives.
CmdoProg2,
I can ping BDP-Server. I found a Microsoft article that describes how to open the SQL Server port. It has a "Microsoft Fix-It" link to set up the port. Should I use this? I haven't used one of these "Fix-It" links before and I want to be careful with our server.
Also, will I have to install SQL Server on every workstation that will access the Windows Sever 2008 application programs or will the Windows Server 2008 SQL Server provide the access? I'm not sure I want to load SQL Server onto every workstation (around 20 of them).
Thanks.
The default port is 1433 and is set on BDP-Server's firewall. I haven't use a "Microsoft Fix-it" link either. On the reference page, it has the procedures on how to open the port on the server. Depending on your network configuration, there may be additional firewalls (I'm not a firewall expert, but are very familiar with network architexture). May want to ask on how to limit IP addressed to the port.
The Server, BDP-Server, provides the engine (access) and SQL Server is not needed on the workstations.
The Server, BDP-Server, provides the engine (access) and SQL Server is not needed on the workstations.
ASKER
CmdoProg2,
I tried to use the "Microsoft Fix-It" and it said that the "Fix-It" wasn't for my version of the operating system, I'm guessing it's Windows Server 2008 v. Windows Server 2008 R2, but again, just a guess. So I went and temporarily turned off the firewall and tried to access the database. I got the same error message. Am I missing something? Thanks.
I tried to use the "Microsoft Fix-It" and it said that the "Fix-It" wasn't for my version of the operating system, I'm guessing it's Windows Server 2008 v. Windows Server 2008 R2, but again, just a guess. So I went and temporarily turned off the firewall and tried to access the database. I got the same error message. Am I missing something? Thanks.
Let me check on some thing... meanwhile I would put your firewall on the server back on.
try specifying the port on the connection string with port 1433 open on the server:
strDataServer = "BDP-Server/SQLEXPRESS,143
also check this thread on a similiar issue
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/acf1d8c7-199c-46bc-a84c-c4778e72d883/
strDataServer = "BDP-Server/SQLEXPRESS,143
also check this thread on a similiar issue
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/acf1d8c7-199c-46bc-a84c-c4778e72d883/
ASKER
CmdoProg2,
We're close! With the SQL Server Configuration Utility I enabled TCP/IP and set the IP Address to the IP Address of the server. When I turn off the firewall everything works. So the question is, how do I get Port 1433 (the BDP-Server/SQLEXPRESS,1433
We're close! With the SQL Server Configuration Utility I enabled TCP/IP and set the IP Address to the IP Address of the server. When I turn off the firewall everything works. So the question is, how do I get Port 1433 (the BDP-Server/SQLEXPRESS,1433
Oops.... drop the port specification, only one of my instances use the IP address with the port specification; otherwise it throw an error.
strDataServer = "BDP-Server/SQLEXPRESS"
SQL uses 1433 and 1434 ports. 1434 is for SQL Browser. I would open ports as needed until
1. Open 1433 and test with the new connection string
2. Open 1434
3. Turn off the firewall
strDataServer = "BDP-Server/SQLEXPRESS"
SQL uses 1433 and 1434 ports. 1434 is for SQL Browser. I would open ports as needed until
1. Open 1433 and test with the new connection string
2. Open 1434
3. Turn off the firewall
ASKER
On the Windows 2008 server I used the "Windows Firewall w/ Advanced Security". I added new Inbound rules to allow Ports 1433 and 1434 and any program. Still no luck. Also, we have Symantec BackupExec on our server and it looks like there are already firewall rules to allow SQL Server and SQL Browser through the firewall. I feel like we're right there. Anything else? Thanks.
Did you drop the port specification on the connection string? I pretty sure you did, but it is the only thing right now I can think is missing...
ASKER
I did drop the port specification. I'm heading out of the office shortly. Let me know if you think of anything else and I'll check back in. Thanks. Have a nice weekend.
I notice that your connection string includes "/SQLEXPRESS". That is of course the default instance name of a Sql Server Express instance. The 2 main differences in SQL Server Express are: NO BACKUP capability and absolutely no network support. You CANNOT connect to an EXPRESS instance from another system.
Sorry, but I hope that helps.
Don't forget to check the right answer.
Sorry, but I hope that helps.
Don't forget to check the right answer.
check "To add a program exception to the firewall using the Windows Firewall item in Control Panel." from http://msdn.microsoft.com/en-us/library/cc646023.aspx
ASKER
SeanDevoy,
I understand what you're saying about SQLEXPRESS and networking. I think I may have regular SQL Server on our Windows Server 2008 machine. I'm not sure how to determine this. However, when I turn off the Windows Firewall my VB program can access the SQL database from a desktop computer. Doesn't this say that I have network support? Let me know. Thanks.
I understand what you're saying about SQLEXPRESS and networking. I think I may have regular SQL Server on our Windows Server 2008 machine. I'm not sure how to determine this. However, when I turn off the Windows Firewall my VB program can access the SQL database from a desktop computer. Doesn't this say that I have network support? Let me know. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks CmdoProg2 and emoreau.
emoreau, I did check the link. I was getting "thrown off" because sqlserver.exe was already in the list of applications, however, it was in the list pointing at the BackupExec folder. When I added sqlserver.exe to the list from the "standard" SQL Server folder everything started to work. Thanks again.
emoreau, I did check the link. I was getting "thrown off" because sqlserver.exe was already in the list of applications, however, it was in the list pointing at the BackupExec folder. When I added sqlserver.exe to the list from the "standard" SQL Server folder everything started to work. Thanks again.