People ask all the time why they can’t connect to their SQL Server. Here are some thoubleshooting steps that they should take in order to find out what the problem is.
Please keep in mind that without knowledge of your specific problem I can only point you towards the right direction.
Windows XP Running Service Pack 2 and higher (This includes Vista and Windows 7)
Windows XP SP 2 and higher 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.
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:
[sql]ping {servername}[/sql]
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:
[code]telnet {servername} 1433[/code]
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 (SQL 2000). 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 (SQL 2005/2008). Log onto the servers console and open the SQL Server Configuration Manager. Navigate to the SQL Server 200n Network Configuration and select the “Protocols for MSSQLSERVER (or YourInstanceName if a named instance). Open the TCP/IP protocol in the right hand pane. On the IP Addresses tab the port number that SQL Server should be listening on is shown.
2c. 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:
[code]SQL server listening on 127.0.0.1: 1433.[/code]
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 they don’t ask you to try them again.
This post is a copy of an FAQ that I posted on tek-tips.com a while back.
Denny
