“Cannot Generate SSPI Context” Error
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
