Return SQL Server Connection Information with sys.dm_exec_connections

Problem

I know SQL Server has a lot of views and functions which I can use to understand what’s going on with my SQL Server. I’m interested in seeing what SQL Server can tell me about the users and applications which are connected. How can I see this information?

Solution

The Dynamic Management View (DMV) sys.dm_exec_connections reports all connections into SQL Server. The into is important because SQL Server doesn’t include internal processes. This makes sense as they aren’t connected to SQL Server.

Return Basic Current SQL Server Connection Information

Let’s take a sample query and see what it tells us:

SELECT session_id, connect_time, net_transport, auth_scheme, client_net_address <br />FROM sys.dm_exec_connections

We’re getting some useful information with only a few columns: the session ID (which we can tie to other) views, when the connection was initially made, how the connection was made (what protocol), how the connection authenticated, and what IP address the client is coming in from, all of which can help us track down a particular connection if we needed to troubleshoot one.

With this query we find that we have a mix of transport protocols, authentication schemes, and what appears to be two different systems connecting. It’s not too different systems, however, as 127.0.0.1 is localhost, or the local machine. The reason we’re seeing a difference is because with TCP as the transport protocol we need an IP address. With shared memory it has to be the local machine.

However, we see more information: two different types of authentication schemes: SQL and NTLM. SQL is self-explanatory. The connection was made using a login created within SQL Server. NTLM, however, means we have a Windows user connection. It’s important to note that it is an NTLM connection and not a Kerberos connection. If all you see if NTLM connections, you can never use the features available with Kerberos, such as Kerberos delegation. Kerberos delegation comes into play when you have to pass credentials across systems. For instance, you are connecting to SSRS with a web browser and your Windows user account is what determines your security against the SQL Server database. In this case, you have to have SSRS be able to pass your credentials on to SQL Server, we call this delegation. However, if we aren’t able to make a Kerberos connection to SQL Server, we can’t do this. So auth_scheme is important.

See What SQL Server Connections Have Been Utilizing SQL Server the Most

This DMV, sys.dm_exec_connections, can also show us how many reads and writes each connection has performed as well as when the last of each occurred. Needless to say, this can be useful information if you’re trying to find the connection and process (session ID) that’s beating up one of your SQL Servers:

SELECT session_id, num_reads, num_writes, last_read, last_write <br />FROM sys.dm_exec_connections

The results show that one of our existing connections is a bit more active than the others. This is a demo system, so none of the numbers are significant. On a production system, especially a heavily used one, you’d expect to see a lot higher number of reads and/or writes.

In any case, this allows us to see if particular connections, especially newer connections have a high number of reads/writes. If so, that may be the problem child(ren) we’re looking for if we’re troubleshooting  an issue in real-time.

Return Last SQL Server Command for Current Connections

One last thing we’ll look at with sys.dm_exec_connections. That is the most_recent_sql_handle column. It contains a varbinary value that we can use with a different dynamic management object, sys.dm_exec_sql_text. In this case we’ll need to use CROSS APPLY and pass the handle in. Here’s how we can do so:

SELECT DEC.session_id, T.text AS 'SQLQuery'<br />FROM sys.dm_exec_connections AS DEC<br />CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS T

Here are out results:

If we compare with our previous results, we now can understand why session 51 had the most I/O. What is interesting is that session 51 issued a SELECT statement. However, it has a high number of writes. Unfortunately, sys.dm_exec_connections won’t tell us why. However, by using other DMVs, which we’ll cover in future tips, we may be able to tell. In this case, session 51 was Excel making a data connection to SQL Server. It’s entirely possible that data was written to TempDB.

Next Steps

• Peruse all the tips at MSSQLTips.com on Dynamic Management Views.
• See another explanation of getting the last SQL query using sys.dm_exec_connections.
• Understand when you must have Kerberos over NTLM for authentication.

K. Brian Kelley

Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.

• MSSQLTips Awards: Author of the Year Contender – 2015, 2017 | Champion (100+ tips) – 2014