List connections with their authentication scheme
Query showing the current connections with their authentication scheme (Kerberos, NTLM or SQL), transport and client address.
This query lists the current connections with their authentication scheme (for example KERBEROS, NTLM or SQL), the network transport, and the client and local addresses and port.
SELECT sys.dm_exec_connections.session_id AS SPID,
sys.dm_exec_connections.connect_time AS Connect_Time,
DB_NAME(dbid) AS DatabaseName,
loginame AS LoginName,
program_name,
sys.dm_exec_connections.auth_scheme AS Auth_Scheme,
sys.dm_exec_connections.net_transport AS Net_Transport,
sys.dm_exec_connections.protocol_type AS Protocol_Type,
sys.dm_exec_connections.client_net_address AS Client_Net_Address,
sys.dm_exec_connections.local_net_address AS Local_Net_Address,
sys.dm_exec_connections.local_tcp_port AS Local_TCP_Port
FROM sys.sysprocesses
RIGHT OUTER JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid = sys.dm_exec_connections.session_id
ORDER BY Auth_Scheme, Net_Transport
Notes
- The join is an outer join on the connections, so every connection is listed. A connection without a matching row in
sys.sysprocessesshows NULL for the database and login columns.