http://www.sqlservercentral.com/blogs/brian_kelley/2008/08/07/check-authentication-scheme-kerberos-on-sql-server-2005/

Printed 2014/07/31 06:56PM

Check Authentication Scheme (Kerberos) on SQL Server 2005

2008/08/07

I had to redo SPNs today because we swapped out service accounts on some of our non-production SQL Servers. I wanted to verify that connections in bound were being made with Kerberos. If you've ever dealt with this, if the SPNs are wrong you usually get an SSPI error, but just to be safe, I wanted positive confirmation that the connection was Kerberos. Here's the quick and dirty query to show the connections and the mode by which they are connecting:

SELECT
       s.session_id
    ,  c.connect_time
    ,  s.login_time
    ,  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

If you have a lot of connections you'll probably want to filter by host_name or by the login, but I didn't have a need to for what I was doing.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.