How to Troubleshoot Connectivity to a Named SQL Server Instance (part II)

This post is a continuation of How to Troubleshoot Connectivity to a Named SQL Server Instance (part I) where I touched on how data providers connect to SQL Server instances. Here I’ll discuss some common problems and how to troubleshoot them.

Actually, the problem I’ll be talking about is only one but it can affect two different services and it is troubleshot in two different ways. The problem I’m referring to is broken network connectivity.

Probing connectivity to a SQL Server instance

Since the TDS protocol (the protocol used by client data providers to communicate with SQL Server) is connection-oriented, it is TCP-based. That makes probing connectivity trivial. All you need to do is try to open a telnet connection:

>telnet example 1433

where “example” is the server name and 1433 is the port where the SQL Server instance is supposedly listening. If the command screen goes blank and you can’t type anything, then a connection has been established, i.e. you have connectivity. If connectivity is broken, you’ll see an error message that a connection cannot be established.

Note: since Vista, the telnet client program is not enabled by default. You have to enable it through: Control Panel > Programs and Features > Turn Windows features on or off.

Probing connectivity to SQL Browser

This is a more difficult task compared to probing a SQL Server instance, because the protocol SQL Browser “speaks” is UDP-based, i.e. there is no connection that ever gets established. The only way to probe connectivity is to send a correct protocol request message and to verify the received response.

Luckily there is a tool, portqry, freely available from Microsoft, that has a built-in knowledge about a number of protocols including the SQL Browser protocol. You can use it like this:

>portqry –n example –p UDP –e 1434 –cn !ms-SQL-m!

If the query reaches a SQL Browser service (and a response is received), portqry will show status of “LISTENING or FILTERED” and some result in the form of a name-value pair collection, e.g.:

ServerName example
InstanceName MSSQLSERVER
IsClustered No
Version 10.0.2531.0

Otherwise the status will be “NOT LISTENING”.

Common causes of broken connectivity

Before trying to change anything, check that the services are running and are listening on the ports you expect them to be listening on.

Other than that, the most common cause of broken connectivity is the Windows Firewall on the server machine. Unless explicitly allowed, incoming traffic to SQL Server instances and SQL Browser is blocked. To fix that, you need to create a custom rule on the Firewall to allow incoming traffic to all the SQL Server instances as well as to the SQL Browser. (I personally find using the program paths rather than the exact port numbers to be more maintainable.)

The second reason why connectivity may be broken is a router on the way that doesn’t have a rule to route SQL Server traffic. You can get the route using tracert on the client machine:

>tracert example

Then check with the administrator of each router whether SQL Server traffic is routed.

If you still have problems with connectivity, post a question on the SQL Server Data Access forum.