Well, I’ve been fighting Kerberos so I thought perhaps this might be a good time to document it.
Hand up if you love Kerberos? …If you like Kerberos? ..If you have to live with Kerberos?
Well, anyway, it’s often a necessary evil. Let’s start at the beginning.
Event Log>Application Log>Error:
"Login failed for user ‘%.*ls’. The login is a SQL Server login and cannot be used with Windows authentication.%.*ls"
First stop – check if SQL Server is set to mixed mode authentication or not. Also determine whether the client connection is trying to pass SQL login or use Windows Auth. In my case I know that I’m not using SQL Logins, and the server isn’t in mixed mode (it’s Windows Auth only). The username is NULL which is a sure fire sign of a delegation, Kerberos, AD/Auth problem. It also helps to check if you can authenticate to the SQL Server instance yourself (try locally, then remote).
Now, let’s assume you’ve verified that the client and server can both resolve each other by name and IP address, and that they both are on the same domain (which, if you have an event log entry (audit failure) is probably redundant). If you don’t know how to do this, probably it’s best to stop reading now and seek an experienced IT Professional.
Firewall. Check to see that the various required ports are unblocked. Chances are that if you see an Audit Failure, ports aren’t your problem, especially if all network operations appear to be working fine. Try accessing network file shares from both client and server. Try browsing IIS servers too, especially ones which expose sites requiring Windows Auth.
Verify you are or are not using Kerberos on your Active Directory forest. Sometimes you might get away with NTLM (unless you have multi-hop authentication or certificate/security token requirements). If you know you have Kerberos, this could be the cause of your authentication pain.
There are some good tools you will want to use right now. The first is SetSPN.exe which ships with the Windows 2000 Resource Kit. Grab a copy from this location here.
The second is the ADSI tools which ship with Windows Server 2003 Service Pack 2 Support Tools, available from here.
Lastly, there is a decent VBS script you can use to query SPN states at this location.
You can use the query created by the SPN VBS script to determine whether an SPN has been created for a specific service instance per machine, per user credential.
Executing this command: spn_query.vbs spn_query.vbs MSSqlSvc/Server1 will query Active Directory for the appropriate SPN entry.
SetSPN is a useful utility for adding and removing SPNs, but be aware you will need permissions on the domain to use it.
Also, while I’m thinking about it, if you want to see what domain policies are being applied to your account, open a command prompt and type: gpresult
If you get impatient waiting for group policy updates to propagate, you can also use this command: secedit /refreshpolicy user_policy (windows 2000, I believe)
For newer OSes, try gpupdate [options]
As I’ve been digging, I found this really long but very helpful post which details just about anythign you’d like to know about NTLM vs Kerberos.
This was particularly helpful – the decisions the OS makes to determine a Trusted Authentication:
"Under condition that you are using Integrated Security or trusted connection which use windows authentication.
1) Kerberos is used when making remote connection over TCP/IP if SPN presents.
2) Kerberos is used when making local tcp connection on XP if SPN presents.
3) NTLM is used when making local connection on WIN 2K3.
4) NTLM is used over NP connection.
5) NTLM is used over TCP connection if not found SPN."
<This post is being updated, check back soon>
