SQL Query to identify Kerberos or NTLM connection

10 Apr 2009

SELECT
    s.session_id,
    c.connect_time,
    s.login_time,
    s.login_name,
    c.protocol_type,
    c.auth_scheme,
    s.HOST_NAME,
    s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

 

A query to see if a connection is made with Kerberos or NTLM.
login_name together with host_name and program_name will identify the login.
auth_scheme will reflect what security protocol was used

Share This:
  • Print
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • PDF
  • Twitter

2 Responses to SQL Query to identify Kerberos or NTLM connection

Avatar

Shuwi

April 10th, 2009 at 11:00

you forgot the comma like so:
SELECT
s.session_id,
c.connect_time,
s.login_time,
s.login_name,
c.protocol_type,
c.auth_scheme,
s.HOST_NAME,
s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

thanks & regards!

Avatar

Marc

April 10th, 2009 at 11:08

Thx Shuwi,
I corrected it ;-)

Comment Form