SQL Server Power Search - Get More Relevant Results

Error Establishing Connection to Server... What To Do

The full text of this message in SQL Server 2005 is:

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.

 

If I had a penny for every time I have helped someone with this error message I would be a rich man! The message means that either the server does not exist, it cannot be accessed, or it is refusing connections.

Lets tackle each of these in turn.

The server does not exist

You probably know that the server does exists. After all, you have installed or configure SQL Server on it, or you are sitting in front of it right now!

If this is the cause it is probably because you have incorrectly formatted the connection string, or specified an invalid instance name. Remember if connecting to a clustered instance to specify the virtual server and instance, not the server name.

If you are happy that these are OK, and the server really does exist, go on to the next possible cause.

The server cannot be accessed

Check that there are no firewalls blocking the request. You need to consider firewalls on the client, server and any others in between. Firewall configuration differs from one to another so I won't go into details here. You, or your network administrator, should ensure that the applications or ports are added to the firewall exception list.

Check that the server is accessible on the network. Type
ping <servername>
and
ping <ip address>

If the first does not get a response but the second does it indicates a possible DNS issue. If neither work it indicates a network or routing problem. Get your network administrator to investigate. You do not need DNS to connect so if the ping with ip address has worked, try connecting to SQL Server by specifying the ip address. If you prefer you can create an alias using SQL Server Configuration Manager.

Provided you can ping the server, you need to check you can connect through the correct port. A default instance uses port 1433. This may have been changed by a DBA (good security practice). A named instance will use a dynamic port, unless this has also been specified.

To find out what port is currently being used look in the error log at the time the service was last started. You should see a message of the form Server is listening on ['any' 1433].

Try to connect using telnet, as follows:
telnet <servername> <port>
You can substitute ip address for server name if you have DNS issues (as explained above).

If you see a command prompt, you have connected successfully. If not, go back and double check your firewall settings. I'm assuming that the ping worked at this point, so the server is accessible on the network.

The server is refusing connections

So we've established that the server is on the network and responding to basic network requests, but SQL Server is still not responding to our connection attempt.

This means that it is not configured correctly, and there are several possible reasons for this.

To start with, check that remote connections are enabled (as suggested in the original error message). To do this, open SQL Server Surface Area Configuration, click on Surface Area Configuration for Services and Connections, expand Database Engine and click on Remote Connections.

Make sure that Local and remote connections is selected.

Another reason for connection failure might be due to the way ports are handled by SQL Server. Before explaining how the client and server agree on which port to use, try adding the port (the one you used for the telnet) to the connection string. If you are connecting to a default instance on SVR-TEST and it is listening on port 12345 change it to
SVR-TEST,12345
If you are connecting to a named instance called INST1 change it to
SVR-TEST\INST1,12345
If this works, you may be happy enough to connect this way, but read on for a more elegant solution.

When a client attempts to connect to a default instance it always tries port 1433, but how does it know which port to use for a named instance? The answer lies with the SQL Server Browser service. This service listens on UDP 1434. The client sends a request to UDP 1434 and the SQL Server Browser service sends back the port number for the required instance. The client then makes the connection by specifying this port number. One benefit of this over including the port in your connection string is that it allows dynamic ports to be used. You don't want the port to change next time the server is rebooted, preventing applications from being able to connect.

To start the SQL Server Browser, first make sure it is enabled. Bring up the SQL Server Configuration Manager, click on SQL Server 2005 Services, right click SQL Server Browser and select Properties. Click on the Service tab and check the value of Start Mode. This should be Automatic.

Once it has been changed to Automatic, right click the service and select Start.

Summary

This article has covered the most common reasons for the connection error quoted at the top of the page. In 99% of cases the cause will be one of those mentioned. If you have encountered any other common reason for the error, I would be interested to hear of it. Please contact me with the details.