I arrived at a client’s office one Monday morning only to find absolute chaos. We had users and applications on servers giving the lovely “Cannot Generate SSPI Context” error all across the organization. At first I wasn’t too worried.  I instantly assumed DNS was the culprit, as this SQL server has been in production for  around 6 months. Well, to say the least, that was a quick 5 minute check and all looked fine. Moving forward, I decided to change some of the System DSNs to use Named Pipes instead of TCP/IP. This is where we had success, and this is what we implemented for our applications/users as a temporary workaround until we were able to successfully resolve the SSPI error. Below you’ll find the steps I performed to fix this problem.

Problem:
Connecting to a SQL database using Windows Authentication over TCP/IP returns the error “Cannot generate SSPI context” whether it’s the application, or when you’re, setting up a User or System DSN for Microsoft SQL, for example.

Workaround:
If you change the DSN to use Named Pipes instead of TCP/IP on the client configuration area of the setup, the SQL Server connection will succeed.

How to troubleshoot the “Cannot generate SSPI context” error message
http://support.microsoft.com/kb/811889

Security Account Delegation (Using the setspn.exe utility to view/add/delete SPNs)
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx

Solution:

1. Stop the SQL Server service
2. Downloaded and installed the SetSPN utility found in the Windows Server 2000 Resource Kit Tools. http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en
   1. Installed on Standard Server 2003
3. Run the setspn.exe utility from the resource kit command prompt. You want to use a –l username or –l computername. This will only LIST the current SPNs configured for either account. (You’ll want to use the –l computername if the SQL Server Service is running as LocalSystem OR a regular domain user) If the SQL Server Service is running as an administrative account (Domain Administrators/SQL Administrator) you would want to use the –l username to see the SPNs registered for that account.
   1. Setspn.exe –l clientdomain\Administrator and setspn.exe –l SQLSERVER1
      1. This showed multiple conflicting or non-updated SPN records for the both the user account and the computer account
4. Ran setspn –D MSSQLSvc/SQLSERVER1.clientdomain.local:1433 Administrator (Run as domain admin) to manually delete all SPN records for anything that had to deal with SQLSERVER1.
   1. In this client’s case, SQL had been setup to run as the domain Administrator, so I listed all SPNs for both the Administrator and the SQL Server hostname. Then manually deleted all registered SPNs for both the Administrator account, and the SQL server hostname.
5. For the time being, I also went ahead and changed the SQL services to run as LocalSystem.
6. Since the services are now configured to run as LocalSystem, you want to make sure to mark the computer account of the SQL Server as Trust for Delegation (Kerberos only) on the Delegation Tab of the computer properties in AD.
7. Reboot SQL server – This will ensure when SQL Server starts, it will attempt to register it’s SPN with your DC.
8. At this point I was able to successfully connect to all the database using Windows/Integrated Authentication over TCP/IP

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website