LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon