I don’t like to write about client situations, but this one seemed worth mentioning for the sake of other people experiencing the same thing, so I asked my client for permission and they agreed.
Following an on-prem server reboot, anything that tried to connect to SQL Server on that server, using Windows Authentication, was getting an error:
The target principal name is incorrect. Cannot generate SSPI context.
- It allowed connections from the local box (but remoting into servers is not recommended).
- It allowed connections that used SQL Logins.
- The error was occurring in Azure Data Studio, SQL Server Management Studio, Invoke-SqlCmd in PowerShell. (For a while, it was allowing connections through sqlcmd, but that stopped working too and I didn’t manage to reproduce it.)
- Kerberos Configuration Manager suggested everything was normal.
For a while it could be fixed by rebooting again. But not reliably. Sometimes it would take several reboots. I suspected that the box was attaching to a different logonserver on the times it worked, but my access wasn’t deep enough in the system to be able to do proper troubleshooting.
I could see that there was a KRB_AP_ERR_MODIFIED error on the server:
The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server ***. The target name used was ****. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. 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 is different from the client domain, check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.
It turned out that the problem was a mismatch in the supported encryption types between the service account in Active Directory and the host / client in Group Policy / Local Security Policy. This article “Decrypting the Selection of Supported Kerberos Encryption Types” shows more information at some depth.
But knowing how tricky it was to find the answer to this, I thought a blog post would be in order.
@robfarley.com@bluesky (previously @rob_farley@twitter)
