SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Check Authentication Scheme (Kerberos) on SQL Server 2005

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:

    ,  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.

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.