I’ve been a Microsoft SQL Server user for about 12 years now going back to the SQL 6.5 version so it was a bit surprising when I encountered numerous problems installing the SQL 2005 client on my new Windows Vista workstation. While trying to connect to my production SQL server I received an error similar to the following.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Well I was sure the Server was allowing remote connections since other workstations including my older Windows XP workstation and my Windows XP notebook were connecting fine. After temporarily disabling my Windows Firewall I discovered that the connections went through fine. The culprit was found!
Nevertheless, I googled (yes this is a valid verb now) the error message and found the following suggestions to be useful.
The first thing you need to make sure of is that SQL server is in fact setup to accept remote connections. To check and/or set this you need to use the Server Surface Area Configuration tool. When this opens, choose Surface Area Configuration for Services and Connections. Under the appropriate server (if more than one server is listed) click on the Database Engine node and expand it. Click on Remote Connections. Make sure that Local and remote connections is selected. You can also choose Using TCP/IP and named pipes if you want to make sure that the SQL server is listening for all protocols, but setting it to Using TCP/IP only should be fine.
The next thing you need to determine is whether the SQL Server instance is using a Static Port or Dynamic Ports. By default, the static port is selected during the SQL Server installation process and set to port number 1433. Here’s how to determine if you are using static or dynamic ports.
Open up the SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on the entry that shows Protocols for <server> where the server is the name of your server instance. On the right, TCP/IP should be enabled. Double-click on this entry. When the dialog opens, click on the IP Addresses tab. You should see each IP address associated with the server as well as the loop-back IP Address of 127.0.0.1. If the entry next to TCP Dynamic Ports is zero or another integer value then your SQL server instance is setup for Dynamic Ports.
Another way to determine what ports SQL server is using is to try the netstat command line utility. Try the following syntax in the command line and look for the IP address of the SQL server.
netstat -n
The problem with Dynamic Ports and your Windows Firewall is that it is definitely possible that the dynamic port assigned by SQL server will be blocked by any communication attempting to come through your Firewall. You request access to the SQL server and it attempts to return information through the Firewall, except that the assigned port is blocked. You could figure out what port has been assigned by checking the Configuration Manager (mentioned above) and open that port through your Firewall, however the port will be reassigned the next time SQL server is restarted.
The best solution is to find a unique port that works on SQL server. The easiest thing to do is to use the last dynamically assigned port number since you are certain that this port is unique. In the IP Addresses tab, clear out the port number next to each TCP Dynamic Ports entry. Then assign the new port number to each TCP Port entry. Then click the [Apply] button. Finally, stop and restart your SQL server instance.
Next, you need to open the new port on your Windows Firewall. To do this. go to the Control Panel and open up the Windows Firewall. Click on the Change Settings link and then click on the Exceptions tab. Click on the [Add Port] button. For the name enter Static TCP Port. Enter the port number that you assigned in the Configuration Manager and then select TCP for the protocol. Finally, click the [OK] button to complete the exception. This will allow SQL Server traffic to flow through your Firewall.
Now you can test your remote SQL server connection and if you’ve followed the instructions above, everything should work fine.
