Finding Which Network Protocol Each Session Uses

The network protocol each session uses is one column away: net_transport in sys.dm_exec_connections. It tells you whether a connection came in over shared memory, named pipes or TCP. What it cannot tell you is who the person on the other end is.

Why anyone asks

The security team wants to switch off named pipes. Before you agree, you need to know if anything still uses it. Or an auditor asks how many connections are unencrypted. Either way, you do not want to guess. The server already knows how every session connected.

Start small. Ask about your own connection, using @@SPID to find your row. The query returns the transport, the protocol type, whether the connection is encrypted and the authentication scheme. It also returns the client address, and a flag for whether a local TCP port exists.

SELECT net_transport, protocol_type, encrypt_option, auth_scheme, client_net_address,
       CASE WHEN local_tcp_port IS NULL THEN 0 ELSE 1 END AS has_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

The result depends on how you connected, so read it as a pattern. My connection came in through Shared memory, which is the local protocol, with the protocol type TSQL. Encryption was on, and the authentication scheme was NTLM. The has_tcp_port flag is 0, and the client address reads <local machine>.

That NULL port is not a broken connection. Local protocols simply have no TCP port. Read the transport first, then decide what the other columns mean.

Count every connection by protocol

Now widen it to the whole server. This query groups all connections by transport and encryption, with no names, no addresses and no logins. It needs permission to view server state, so a plain user may see less than an administrator.

SELECT net_transport, encrypt_option, COUNT(*) AS connections
FROM sys.dm_exec_connections
GROUP BY net_transport, encrypt_option
ORDER BY net_transport, encrypt_option;

On my test server, I got two rows, both with encryption on: Shared memory, which is how the demo connects, and a Session row. A Session row is a logical session that rides on another connection, so it is not a separate protocol. Your server will show a mix. If a row says Named pipe, you have found someone still using it. Find out who before you switch it off.

A connection is not a person

Be careful with the address column. Behind a web server, a proxy or a connection pool, hundreds of people can share one visible address. The server sees the last machine that connected, not the human who clicked a button.

If the database needs to know the real user, the application has to say so. SESSION_CONTEXT is one way. The application sets a key after it authenticates someone, and the database reads it. The demo sets a marker, reads it, then clears it.

EXEC sys.sp_set_session_context @key = N'RequestSource', @value = N'OrderPortal';
SELECT SESSION_CONTEXT(N'RequestSource') AS request_source;

EXEC sys.sp_set_session_context @key = N'RequestSource', @value = NULL;
SELECT SESSION_CONTEXT(N'RequestSource') AS cleared_request_source;

The first result is OrderPortal. After clearing, it is NULL. That is exactly why the marker is only a label. Any code on the connection can set it, so never use it as an authorization decision unless a trusted layer controls it. And reset it at the end of each request, or the next user of a pooled connection inherits it.

Check it on your own server

Run the first query from SSMS on your own machine, then from another machine, and compare the transports. Your server’s protocol settings decide which ones work, so try one that is already enabled. A failed attempt leaves no row to look at.

A report across all sessions needs the right monitoring permission. Test the account that will run it, not just your own admin login.

Run the counting query once this week, and see what is actually connecting.

A connection endpoint is not a person, it is the network peer the engine accepted.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.