Welcome to Era of Data
home || about || contact || blog ||





Posts Tagged ‘connectivity’

Verifying and troubleshooting SQL Server listener connectivity

Tuesday, March 3rd, 2009

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.


2009-02-24 08:15:40.050 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2K5DEV ].
2009-02-24 08:15:40.050 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2K5DEV\sql\query ].
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
Named Pipes np np:\\.\pipe\MSSQL$SQL2K5DEV\sql\query
Shared Memory lpc lpc:.\SQL2K5DEV
TCP/IP tcp tcp:.\SQL2K5DEV,50334
Note the use of the TCP/IP port number
VIA Protocol via via:.\SQL2K5DEV,1433,0
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.

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.

Happy troubleshooting!

Tags: , , , , ,
Posted in SQL Server | No Comments »