Troubleshooting SQL Server listener connectivity

SQL Server listeners

Verifying and troubleshooting SQL Server listener connectivity via tcp, named pipes or shared memory connections is a key component in a DBA’s armoury but often overlooked until it’s too late and application teams call up complaining their apps can’t connect.

SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes, but sometimes it’s necessary to check connectivity by a specific listener.

The first place to check is the error log to confirm that the relevant listener has started up; the error log is the SQL Server version independent way of confirming listener startup instead of relying on e.g. the sys.endpoints catalog view available from SQL Server 2005 onward:

select name, protocol_desc,state_desc from sys.endpoints

SQL Server listener status

Use the SQL Server Configuration Manager (SQL Server Network Configuration Section) to view and enable or disable the various listeners. A SQL Server restart will required to action any changes.

The output below is from a SQL Server 2005 server with TCP/IP, shared memory, named pipes and the dedicated administrator connection listeners enabled.

2009-02-24 08:15:40.050 Server Server local connection provider is ready to accept connection on [ .pipeSQLLocalSQL2K5DEV ]. 2009-02-24 08:15:40.050 Server Server named pipe provider is ready to accept connection on [ .pipeMSSQL$SQL2K5DEVsqlquery ]. 2009-02-24 08:15:40.050 Server Server is listening on [ ‘any’ 49839]. 2009-02-24 08:15:40.050 Server Dedicated admin connection support was established for listening remotely on port 49839

So, the relevant listener is in the list and, as far as SQL Server is concerned, is up and running. The next step is to check connectivity. The SQL Server clients are best for this, as this will take the problem client application out of the equation.

Either SQL Server Management Studio (SSMS), or the lightweight console mode SQLCMD, which is my preferred tool will do here.

Testing the listener

To test each listener you just need to prefix the listener name abbreviation before the server name. Using SQLCMD as an example and a named instance by of SQL2K5DEV:

Replace «hostname» with the name of the server you are trying to connect to.

If you prefer to use SQLCMD, just put the listener prefix in front of the server name as listed in the prefix column after the -S (server name) parameter.

When specifying the port number for e.g. the tcp/ip connection there is no need to specify the instance name. If the machine/hostname is ALPHA and the instance name is BETA you don’t have to specify tcp:ALPHA\BETA,50334 as specifying tcp:ALPHA,50334 will be sufficient to make a successful connection.

If you’re testing locally on the server itself (or your own dev box) replace hostname with ‘.’ as a shortcut (without the quotes) and save yourself some typing.

Testing connectivity from a remote client

To test connectivity from a remote client, you don’t need the sledgehammer of an SSMS install, just right-click on the desktop and create a new UDL file by selecting New > Text Document:

Creating a UDL file to test connectivity

Make sure the filename extension is changed from .TXT to .UDL (e.g. call the file test.udl) and click Yes when warned about changing the extension. Creating a UDL (Universal Data Link) file invokes Microsoft’s Data Link tool for specifying OLEDB, ODBC or SQL Native connections from a remote Windows client:

Setting connection properties to test connectivity

Enter the appropriate connection details and click on Test Connection to perform a quick connectivity check. Clicking on the Provider tab will display all the drivers available on that system:

Connectivity drivers available on a Windows client

The beauty of checking connectivity using this technique is that it takes the client application that is having the issue totally out of the equation. If this test works, it’s an issue within the application and not a database or firewall issue.

The SQL Browser service

The SQL Browser service’s principal responsibility is to direct incoming connections to the relevant named instance.

If you’re having issues connecting to a named instance, and this service is enabled and running, make sure your firewall has an exception of UDP port 1434 which is used by this service for this connection handling.

If SQL Browser cannot be enabled and you have named instances running on that host, remote clients can still connect by specifying the port number after the hostname. Just specify myhost.domain.com,5000 in the Server name text box of a connection via SSMS to connect to a named instance listening on port 5000 on server myhost.domain.com (note the lack of any reference to the actual instance name as we only need to provide the host name and port number).

Verifying what listener a connection is using

To verify what protocol a connected client is using, use the following query from SQL Server 2005 onwards:

select net_transport, endpoint_id, connect_time,client_net_address from sys.dm_exec_connections where session_id = @@spid

Replace @@spid with the spid (or session_id) of the connection you’re interested in.

This will output the listener the relevant connection is using as well as information like the endpoint id (which can be checked against sys.endpoints), when the connection came in and its address, as shown below (I dispensed with the client_net_address column to make the output easier to read):

If you can confirm the listener is up and you can connect to it (at least locally) you know it’s not a SQL Server configuration issue and is more likely to be a firewall/network problem, so make sure the relevant ports you’re listening on are open, e.g. named pipes required port 445 to be open.

Just to prove it, go to the client machine(s) concerned and verify if the Microsoft port querying tool can connect to that server. Fortunately, there is a graphical interface for this tool. This is a very useful tool for identifying, or at least narrowing down, connectivity problems (make sure you select SQL Service in the ‘Query predefined service’ dropdown).

Happy troubleshooting!