I had an odd issue at work where I could not connect to a SQL server using Windows authentication even though I had the authentication set to mixed mode. I was able to logon with the SQL authentication details but not with Windows authentication. I was presented with the following message – “The target principal name is incorrect. Cannot generate SSPI context.” When looking into this it turned out that the SPNs were registered to a user object that did not run the service on the host of where the SQL Server was running. I use service accounts for SQL server, this one named SCCM-SQLSRV and this did not have the SPNs registered to it.
To find out the user object/computer object that the SPNs are registered to you must first run the following command. Replacing $Computername with the hostname of your server and $FQDN with your fully qualified domain name.
setspn -Q MSSQLSvc/$Computername.$FQDN
This will bring back which computer object/user object the SPN belongs to like so:
Checking domain DC=contoso,DC=com
CN=AccountThatHostsTheSPNs,CN=Service Accounts,DC=contoso,DC=com
From this, we see that the SPN is registered to a hypothetical user object named “AccountThatHostsTheSPNs”.
What we must now do is unregister the SPN. For this instead of using -Q we use -D.
Now we must register the SPN, again using the setspn command or if you really wanted to you could go to the user object in AD and go into attributes editor and do it that way but for the purpose of this guide, I will stick to using setspn. So this time we use the A parameter and append the user object to the end of the command to which you would like it registered to. If you’re using local system on the SQL service then this would be the computer object. If this is a user object, you must also make sure that the user object has AD permissions on the computer object of the SQL server to read and write Service Principal Names.
setspn -A MSSQLSvc/$Computername.$FQDN $UserObjectYouWantToRegisterTheSPNTo
You test that this works by issuing a kerberos ticket for this SPN, I did this initally which pointed me in the right direction as to what was wrong. You can also test and find out which user object it should be registered to this way.
Previous to doing this fix, when I issued a kerberos ticket, I would get the following critical warning in the event log. Which pointed me to the solution to why this error was coming up and I could not authenticate.
The Kerberos client received a KRGB_AP_ERR_MODIFIED error from the server SCCM-SQLSRV. the target name used was MSSSQLSvc/sccm.contoso.com:1433. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target principal name (SPN) is registered on an account other than the account the target service is running. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (contoso.local) is different from the client domain (contoso.local), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.
Remember to restart the SQL service once you’ve updated the SPNs. It should then work. I hope this helps somebody as I was coming up blind trying to google this one.
