SQL Server: Cannot Generate SSPI Context and KRB_AP_ERR_MODIFIED Errors
As DBAs, one of the most common tasks performed is the installation of new SQL Servers. While I've installed hundreds of SQL Server instances over the years, I only recently come across the error described below. In the past, I've seen the SSPI context error, but it is usually tied to an issue with a domain controller, or the OS in general, and a reboot often solves the problem. However, in this circumstance, the issue lies in Active Directory and SPNs (Server Principal Names).
In the past, I had the ability to create SPNs in Active Directory. When SQL Server installs with an account having these rights, an SPN is created that ties the host to the service account used to run the SQL Server. Along the way, that right was removed from my group and our service accounts. To understand the significance of this, you have to know a little about how Kerberos Authentication is working in the environment. That's exactly what I know. A little about it. I'm not an expert by any means and that's for my department's Windows Server team to fully understand.
What I do know is that when a client connects to a SQL Server using Windows Authentication, it will try to use Kerberos. The client will ask the domain controller for a ticket to use for authentication with the service account on the server. The ticket is generated for the client and then the client uses the ticket to authenticate with the SQL Server. If you have seen the SSPI error when attempting to log in, which I assume you have if you are reading this post, then there's definitely a problem.
There are a few different set of problems that can be occurring, but in my case, the server name that I was installing once existed many years ago. The analyst that requested the server wanted to reuse the server name. When the original server was created, it was installed using a generic domain account, and my account had permissions to generate SPNs in Active Directory. The SQL installer laid down SQL Server and tied the SPN for that host to the generic domain account.
Fast forward a few years later, and I am trying to authenticate with this new SQL Server running under a new domain account. My client is asking the domain controller for a ticket tied to the SPN with the format MSSQLsvc/<your.server.name>:1433. Note: The server name here should be the FQDN, not just the host name. Active Directory locates that SPN and sees that the service account is GenericSvcAcct. It passes the ticket back to the client and the client then passes this ticket on to NewSvcAcct, and subsequently fails.
Even if you do not have the necessary permissions to create or modify the SPNs, you can still take a look at what is happening.
From a command prompt, type:
1setspn -L mySvcAcct
(where mySvcAcct is the account running SQL Server)
A list of SPNs will display if they exist, or you may see nothing. If you see the entry MSSQLSvc/<your.server.name>:1433, Kerberos authentication should work. If you do not see it, you either have a conflict, or you are not using Kerberos authentication. If the proper SPNs are not in place, you can still use Windows Authentication, but it will fall back to NTLM authentication.
Chances are, there may be another service account out there with the SPN attached to it. If you have any generic SQL Service accounts, list them out and look for your server in the list. If you find it, it will have to be removed and assigned back to the proper service account. Maybe you don't know what service account that SPN is assigned to? That's probably not an uncommon problem. Good news, you can query AD for it as well.
1setspn -Q MSSQLSvc/<your.server.name>:1433
Take a look at the results and determine which account that SPN is tied to. The name of the account will be displayed at the top (i.e. CN=SQL Server Service Account). You will have to determine which account that really is, if not obvious.
In my situation, once I had the SPN deleted from the old service account and created on the new service account, my problems went away. But wait, what if you don't want to mess with all of this SPN business? What if you don't have the rights to create the SPNs in the first place? Then as stated above, you can still use Windows Authentication, but it will use NTLM. You can read more about both authentication protocols and how they relate to SQL Server on the SQL Server Protocols Blog.