In SQL server management studio there are a lot of shortcuts and one of the…
Recap from last week:
Well, you can easily verify that Kerberos validation is active by executing the following query:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
Answer: “KERBEROS” <= YES, you´re all set! “NTLM” <= NO, it´s still not working.
So, you get the annoying “NTLM” reply from the query, now what?
The most common problem is that you have multiple registrations in Active Directory!
Run this in a CMD prompt: setspn -X
This command will list all duplicate registrations in the AD and if you find the following lines:
————————————————————
Checking domain DC=nydomain,DC=local
…
MSSQLSvc/nystromsql.nydomain.local:1433 is registered for these accounts:
CN=nystromsql,OU=servers,OU=up,DC=nydomain,DC=local
CN=sqlservices,OU=SYSTEM,OU=ACCOUNTS,OU=up,DC=nydomain,DC=local
…
You have two accounts registered for the MSSQLSvc service.
The solution to this is simply to remove the duplicate account. The only account that should be listed is the one that you entered in the SETSPN in last weeks session. If Kerberos finds a duplicate, it will always fall back to NTLM!
Problem solved!
//Joakim