Troubleshooting Connections to SQL Server

The purpose of this FAQ is to assist people who are having problems connecting from there workstation to a Microsoft SQL Server. Within this FAQ I will attempt to point out the possible problems and the possible solutions to these problems.

Please keep in mind that without knowledge of your specific problem I can only point you towards the right direction.

[color red]
Windows XP Running Service Pack 2
Windows XP SP 2 by default turns on the Personal Firewall Component of Windows XP. This will by default block your connection to your SQL Server. You will need to open the firewall to allow this traffic, or turn off the firewall.
[/color]

There are several steps that I am going to lay out here to attempt to find the problem. We will be starting with the most basic, and working up to the more complex. Some of the more complex will involve people from network security, network engineering and/or your ISP.

1. Can you ping the server?
From a command prompt type:
If you can not ping the server then you need to make sure that both you and the server are online. (This may not mean that either you or the server are offline. It may simply mean that someone has blocked ICMP packets from passing from you to the server for some reason.)

2. Can you telnet to the SQL Service port on the SQL Server?
From a command prompt type:
If you get a black window with a flashing cursor that means that you have connected to the SQL Server. If you get an error message that means that something is blocking your connection to the SQL Server. Check for firewalls running on both machines, or on the network between you and the server. If the server is hosted by an ISP outside of your company's control, check with them, and with your ISP to ensure that neither of them are blocking port 1433. If they are blocking access to the SQL Server they will have another way for you to access the SQL Server, probably a web based version of Enterprise Manager. There are several on the market, and they all work fairly well.

There are a couple of ways to check what port SQL Server is listening on.
2a. Log on to the servers console, and open Enterprise Manager. Right click on the server and click properties. Click Network Configuration. Check the properties for TCP/IP. It will tell you what TCP port the SQL Server is listening on.

2b. Check the Current SQL Server Log (the ERRORLOG if your looking at the actual file). At the beginning of the log there should be one line for each IP on the server. Something like:
[quote ERRORLOG]
SQL server listening on 127.0.0.1: 1433.
[/quote]
That's the port that it's listening on.

If these lines aren't there, and you've checked the TCP/IP is turned on, then there is probably an error saying that the port couldn't be opened. This means that something else was using the port at startup. Odds are what ever was using it no longer is, and a service restart will fix the problem.

3. Can you connect to the file share on the server?
If this is being blocked that could also explain why you can not get connected. By default SQL Server will use TCP/IP port 1433 as it's default connection method, with named pipes as a backup. If you can't connect to the Windows file share you won't be able to connect to the SQL Server over named pipes.

4. Can you connect to the SQL server from another machine on the same network as the workstation that isn't working? If so then the problem is probably with the nonworking workstation, or some setting on a router or firewall that is preventing this machines access.

5. Are there any firewalls or ACLs on your network preventing access?

At this point odds are you are either connected to the SQL Server or you know why you are not. If you still can't connect post a question in one of the SQL Server forums and someone will do there best to help you. You will want to post the results of these questions so that we don't ask you to try them again.

Please don't tell us that you tried all this stuff if you didn't. We ask these questions so that we can use the information to help troubleshoot your problem. Telling us that you checked this stuff when you didn't will just slow us down in trying to help you.