SQL Server listeners
Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now (hopefully!).
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 (if you prefer to used the DMV,
select name, protocol_desc,state_desc from sys.endpoints will list all the listeners and their status).
Use the SQL Server Configuration Manager (SQL Server Network Configuration Section) to view and enable or disable the various listeners, although a SQL Server restart will be involved in starting/stopping them.
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 (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:
|Listener||Prefix||Example Server Name entry in SSMS|
Note the use of the TCP/IP port number
Note the use of port number and the network card number (0 if it’s the only card)
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.
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, for whatever reason, this cannot be done or SQL Browser is not running and you have named instances running on that host, remote clients can still connect by specifying the port number after the hostname as outlined in the previous section e.g. 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
column to make the output easier to read):
net_transport endpoint_id connect_time
------------- ----------- ----------------------
Named pipe 3 2009-03-03 08:59:07.31
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.