Verifying and troubleshooting SQL Server listener connectivity

SQL Server listeners

SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes.
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.
The output below is from a SQL Server 2005 server with TCP/IP, shared memory, named pipes and the dedicated administrator connection listeners enabled.

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 (presumably you’re reading this blog because of issues connecting to SQL Server via a specific listener).
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 local instance by the name of .\SQL2K5DEV:

In case you didn’t know the ‘.’ where the host name should be prior to the instance name is a shortcut for the local machine name.
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.

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 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.

Happy troubleshooting!

Tags: connectivity, listener, Named Pipes, Shared Memory, SQL Server, TCP/IP

This entry was posted on Tuesday, March 3rd, 2009 at 10:36 pm and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.