Here, I talk about one extreme situation: SQL server was running under Local System and was shutdown accidentally. The user then decides to run SQL server under a different account, e.g local account, domain account etc., for whatever reasons. Then he/she hit this “Cannot Generate SSPI Context” error when the client tries to connect the server. Keep in mind this only happens when TCP is enabled for the SQL server and is used by the client to connect the server.
What happened here is: When SQL server ran under Local System, it had successfully registered the Service Principle Name (SPN) for the service. The SPN is kept in the Active Directory and should be de-registered when the server is shutdown. Due to the accidental shutdown, SQL server failed to de-register the SPN. When the client connects to the server using TCP, it can find the SPN in the Active Directory and Kerberos will be used to perform the security delegation. However, the new account is not the correct container of the SPN, and Kerberos will fail.
When this happens, some people may choose to reinstall SQL Server or even the whole OS. They may be frustrated by the fact that the problem is still there if local or domain account is again chosen as the service account. The SPN in the Active Directory won’t go away even if you reinstall the OS.
can be used to register/de-register SPNs. One can register the same SPN for the same container more than one time. The registration beyond the first registration may not do anything. One de-registration will remove the SPN from Active Directory totally. Because of this, the easiest first step to troubleshoot “Cannot Generate SSPI Context” is to run SQL server under Local System account and gracefully shut it down. You can then change your service account to whatever you want. SPN will not be registered and clients will fallback to use NTLM.
Also note that, if you made any change related to SPN or service account on the server, the cached information on the clients may need a couple of minutes to go away. You may see some inconsistent information during this period. Just wait several minutes in this case.
Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights