“Cannot Generate SSPI Context” Error

Mar 18th, 2009

One of our Database Administrators was conducting a Disaster Recovery (DR) test. They dis-joined a production SQL Server out of the production domain and then add DR SQL server with same name to the production domain. After a successful test, the DR SQL server was then removed from production and they then joined the production sql server to the domain again. But to their surprise, no jobs can run, the error was “Cannot Generate SSPI Context”. Authentication is failing, and they were requested to contact their systems admin.  

Cause: : After the DR SQL server was added to the prod environment,  it started using and took ownership of the Service Principal Name (SPN) that was used by the prod server in Active Directory. So, when the prod sql server was rejoined to the domain, it could not fine its original SPN, thus, when clients connected to server, spn can’t be found and authentication failed because there was no security principal present fot delegation (The spn should have been  deleted when the prod server came back online)

Solution: run a setspn to delete servise principal name of the server and then re add to domain

To delete: setspn -d mssqlsvc/ ServerName.DomainName:1433 ServiceIDName

to readd:  setspn -a mssqlsvc/ServerName.DomainName:1433 ServiceIDName

Another solution option, which is unsecured, is to logon using the  the local system account

Be Sociable, Share!
No comments yet.