Finding Instances That Aren’t Using Kerberos

If you have a central management server set up, or a similar way to run a query against a list, the query below will identify those that are using NTLM instead of Kerberos (or just run on each instance individually). Not necessarily a big deal depending on what you’re doing, but sometimes being able to double hop is useful.

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID and auth_scheme = ‘NTLM’;

If you need to figure out what’s working and what isn’t on a per server basis, the Kerberos Configuration Manager is a useful download. It will show what is and isn’t setup correctly and show you how to fix it, though DBA’s typically won’t have enough domain permissions to do the fix.