DaveWentzel.com All Things Data
SQL Server Connectivity Troubleshooting
Ask many DBAs to perform basic SQL connectivity troubleshooting and they run into problems they can't seem to solve. Some examples...if SQL Server listens on a non-standard port, how do you connect to it if the SQL Browser is not working? How can you tell quickly if a firewall is blocking the SQL port? How can you do any kind of SQL connectivity troubleshooting without using Management Studio? This is the process I follow to diagnose and correct almost any connectivity troubleshooting problem with SQL Server. The key is to always follow the "stack diagnosis" process. Think of SQL communication as a "stack" and ensure each layer of the stack, starting at the lowest level first, is working before moving along. The SQL communication stack is very similar to the OSI model. Don't focus on specific error messages since SQL Server and its drivers will many times throw the *exact* same error messages under multiple, different circumstances. So any given error message may have multiple solutions. Also, different releases of the various tools in the stack will change their error messages rendering troubleshooting by error message much more difficult. For instance, SQL Server "cannot connect" messages in SQL 2000 are very basic, in 2005 the surface area configuration utility was introduced (and subsequently removed in 2008 because it was useless) which caused the messages to change. Lastly, if you understand the communication stack you will understand why various errors are thrown. This process will solve almost any connectivity problem.
Here is the basic SQL communication stack. This is customized for the application I work with which runs on SQL Express. SQL Express, by default, uses a somewhat random port when it is installed. Further, it does not install with SSMS or any of the other graphical tools.
This is a more detailed flow chart with actual steps. You start by working down the left hand side of the chart. The steps are organized based on where you perform the step (the client or the server). The color coded "home plate" icons are links to the expanded documentation for that section found further below.
Before getting into the details it's important to understand what a connection string looks like in SQL Server. I call these strings "monikers." It's just the naming convention required to connect under various circumstances. For most people the servername is the only component that is required for a connection. So if my server is called DAVE then my connection string's "Data Source" will be DAVE.
It's even easier if you need to connect to your SQL Server running locally on your machine. Simply type (local) or a single dot (.). So if I am using a stand-alone app I can connect using
Note that to connect to a named instance I simply append a backslash and the instance name. So if my instance on DAVE is called STP my connection will look like this:
If connecting locally it will be one of these:
With a non-standard port (not 1433) and SQL Browser not running, use this moniker:
So, to connect to the server DAVE, named instance STP, on port 1533 the string passed to sqlcmd/osql is:
Note that if I'm connecting local I don't need to specify a port since communication will be via Shared Memory, not TCP/IP.
Let's go through the "home plate" icons in order. When you can't connect to your SQL Server always ping it first. After that make sure you can telnet to the actual port SQL is listening on. To determine the SQL port, do this:
Using Telnet and Firewalling
Now that we know the port let's attempt to telnet to that port from our client:
If we can't telnet to the server then the port is firewalled. On a corporate network that firewall could be anywhere and you will likely need your network guys to help you troubleshoot further. But first I would check that the server has it's firewall open for the SQL port. If you use Windows firewall the process is simple:
After that I always attempt a connection with osql if I have it handy. osql no longer ships with SQL Server and that is unfortunate since it only requires a single rll for basic connectivity. It requires no installation of .net like sqlcmd does (so we can guarantee there is no .net driver issue at play), it simply requires MDAC which is installed and configured by default on all MS OS's since at least XP. If you don't have osql handy then sqlcmd is your next logical choice. Again, if the SNAC driver is corrupt, or anything in the .net stack is corrupt you won't really know, but that's the best you can do right now. osql commands are very similar to sqlcmd commands. You can download sqlcmd using the Feature Pack for SQL Server.
At this point, any remaining connectivity issues should be from within your app which means you probably have a driver issue. Troubleshooting this could be another week's worth of blog posts. Let's just stick to the basics...
|On the System DSN tab, click Add and then select the driver your application requires.|
|The Name can be anything. The server will be your proper connection moniker, since I use a named instance but the default 1433 port I specify the moniker without the port. Click Next|
|Choose your authentication method and ensure the last checkbox is SELECTED. Click Next.|
|If you see this screen without error then you are connected.||
Help, I still can't connect
If your application still cannot connect then it could be the application did not install correctly. For instance, if your application uses a custom third party component for connectivity (such as an ORM tool) then it could be masking your connectivity issues. I can't help you with that.
Hopefully troubleshooting connectivity issues will be less painful for you now. Remember to always follow a process when diagnosing where the issue lies. I prefer using my stack diagnosis method where I start at the basic physical layer (am I connected to the network?) and move up to the final steps involving the drivers. This method has always worked for me.