Allen White

That Evil --Cannot generate SSPI context-- Message

When I was upgrading my servers to SQL Server 2005 I ran into some problems with permissions, so I decided to focus on one problem at a time, and just upgraded using the admin account I've been using for some time. I know that running SQL Server under an admin account is not a good idea, so I've been working on resetting my servers to run under a domain user account. I modified a set of instances on one development server to use the user account, but was unable to access those servers from my workstation, and got the error message "Cannot generate SSPI context".

The Microsoft Support Site helped me understand the problem, but the site that really turned the corner for me was How to use Kerberos authentication in SQL Server. On that page I learned about the Server Principle Name, and how it has to be set for the execution account for each instance of SQL Server it's running, to allow a client to successfully pass Kerberos tokens between systems, and that's how integrated authentication is handled most efficiently.

First, I used the setspn utility to determine whether the account was registered with the SPN (Server Principle Name) for each SQL Server instance running.

setspn -L sqlacct

I saw that no servers were returned from that command, which was probably why I was getting the error. I then registered the account properly.

setspn -A MSSQLSvc/MySQLServer:1202 sqlacct 
setspn -A MSSQLSvc/MySQLServer:1132 sqlacct 
setspn -A MSSQLSvc/MySQLServer:1155 sqlacct 

Once this was done I was able to connect to those servers, and life was good again. Now to schedule some off hours time to make those changes in my production environment.

Allen