How to check what SQL Server network protocol is used by connections

In this post I would like to share a simple query showing currently running statements (using sys.dm_exec_requsts DMV) along with the connection type, address and network protocol used form the session, executing this statement. The script itself you can find below:

USE [master]
GO
SELECT [servername]= @@servername, 
command,
s.text,
start_time,
connect_time,
net_transport,
protocol_type,
auth_scheme,
client_net_address,
client_tcp_port, 
local_net_address,
local_tcp_port 
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
inner join sys.dm_exec_connections c
	on c.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

Now why would you need that – well, sometimes applications suffer from the fact that the IIS they are using for example, connect to the SQL using Named pipes over network (yes, this is possible). Despite it should be controlled from the application’s connection string, this is not always true and it is left to the DBA to identify and restrict such scenarios as it can really hurt performance or lead to “general network errors” we have all seen. For additional information on SQL Server protocols, their use and explanation – please visit this MSDN article.

Additionally – here is a small article describing how to define connection protocol in the connection string: http://www.connectionstrings.com/Articles/Show/define-sql-server-network-protocol

I am writing this post as for last several weeks I am involved in an application issue that for me was quite difficult to troubleshoot and track down the issue. The application (a simple IIS one) was raising timeout and connection errors such as “[Microsoft][ODBC SQL Server Driver]Communication link failure SQL State: 08S01 EDL error… While investigating with the application team, though, we noticed one strange thing when collecting trace data – despite the application was using SQL login in the SQL Server Profiler trace column NTUserName was sometime blank, sometimes with ANONYMOUS LOGON user. This came very strange to me because I would expect this column to be always blank when we are using SQL Authentication. However I tracked this “effect” to be due the network protocol, used by the connection – Named pipes. It turned out that when Named pipes is used, the connection is triggering NT authentication for the user performing the connection. If (as in our case) this is IIS with identity configured as Network Service, then ANONYMOUS LOGON is the expected result. So as a bottom line – in order to find out the network protocol used by a certain remote connection/session, you can use the query above (extracting running statements with connection info), the DMV sys.dm_exec_connections or use SQL Server profiler. For Profiler have in mind that:

  • if NTUserName is blank and in LoginName you have SQL Login – then it is SQL Authentication through TCP/IP
  • if NTUserName is not blank and in LoginName you have SQL Login – then it is SQL Authentication Over Named Pipes

One Reply to “How to check what SQL Server network protocol is used by connections”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.