Ah, the nuisances of AD and Kerberos. Whenever you change the service account on a SQL instance, you need to make sure that an SPN pointing to the SQL instance is in place for the Service Account. Otherwise, you might get the dreaded “cannot generate sspi context” error. To manage SPNs registered on your service accounts, you will need to use the setspn.exe utility, which is available here.
To see which SPNs are registered with your SQL Service account:
setspn.exe -l <current_service_account>
Unregister the SPN for the old SQL Service account:
setspn.exe -D MSSQLSvc/full.name.to.your.host.here:1433 <old_service_account>Finally, register the SPN for the new SQL Service account:
setspn.exe -A MSSQLSvc/full.name.to.your.host.here:1433 <new_service_account>If you use named instances, the syntax for the command will vary slightly. See this post by Jeff Gilbert which has information on syntax to use and other details.
You could also get SSPI errors if there is no registered SPN for the SQL Service on the host itself. See Jeff Gilbert’s post for details on that.
I miss totally the capacity to understand wat an SPN is. I red a lot on the Internet but I keep not understanding the concept. Is there anybody who is able to explain it clearly?
I like this explanation. Let me know if it helps.
Great job tackling an annoying and common problem!
This might help:
Configuring Kerberos Authentication
[...] This post was mentioned on Twitter by afernandez and Tampa Bay SSUG. afernandez said: [NEW BLOG POST] SQL Service Accounts and SPNs (Service Named Principals) http://bit.ly/3XcdeR #SQLServer [...]
Actually I finally understood the concept. I still do not see how it could possibly work but I know that’s my brain that does not develop the right neuronal connections!
Many Thanks!