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

A common issue I have observed at the SQL Server Data Access forum is troubleshooting connectivity to named SQL Server instances. IMO the troubleshooting difficulty is due to the correlation of multiple factors.

Spotting a Named SQL Server Instance

How do you know that you are connecting to a named SQL Server instance? If you reference your SERVER or Data Source as ‘foo\bar’, then you are trying to connect to a named instance ‘bar’ on machine ‘foo’.  The most typical example of a named instance is SQL Server Express that by default creates an instance named ‘SQLEXPRESS’.

Connecting to a SQL Server Instance

How do data providers find the right instance (especially when there are multiple instances on the same box)? For the sake of simplicity I’ll only discuss connectivity over TCP/IP. That could easily be applied to shared memory and named pipes.

In order for the data provider to establish a TCP connection to the target SQL Server instance, it needs: 1) a machine name/address, and 2) a port number. There are three ways for applications to reference a target SQL Server instance:

1. Explicitly
2. As a default instance
3. As a named instance

Using Explicit Properties

An explicit reference looks like this: ‘tcp:foo, port’. That gives the data provider all the needed information. For the purpose of troubleshooting, you can use that notation to eliminate other factors from the picture.

Using Default Properties

The second way to reference a target instance is by not specifying the port (and protocol), i.e. ‘tcp:foo’ or just ‘foo’. In this case the data provider pulls settings from the Registry on the client machine to find what the default TCP port is. (In the latter case it also pulls information on what connectivity protocols are enabled.) That information is easily accessible through SQL Server Configuration Manager. Navigate to SQL Native Client 10.0 Configuration > Client Protocols. Right-click on TCP/IP and select Properties. You’ll see the Default Port property. In the Registry, that information is stored under: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI10.0\tcp.

Using an Instance Name

The above notation doesn’t let you connect to different SQL Server instances on the same target machine. Therefore there is the server\instance notation that is as powerful as the explicit one but is much easier to manage. The mapping of instance names to port numbers (actually, to protocols and protocol-specific properties) is done by a separate service – SQL Server Browser a.k.a. SQL Browser. Since the application never references SQL Browser explicitly, the latter must be listening on a well-known port and protocol, and that is UDP port 1434. When the data provider sees a named instance reference, first it asks the SQL Browser service on the target server (at UDP port 1434) to resolve the instance name name to protocol-specific properties, and then uses those properties as they were provided explicitly.

(To be continued.)