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.
