There are hundreds of posts floating around about SSPI failures in trying to authenticate to SQL Server, so I thought I would add another one!
Because this was a new one to me…and I was on the security team at Microsoft support 10 years ago that dealt with SSPI errors.
We have a corporate policy here that Service accounts expire after a certain time frame, and yesterday morning was the designated window to change them and update the SQL Server services.
Being cautious, we started with just one.
- Changed the password.
- Tried and failed to log on to the server with that account (which should have worked)
- Tried again. Still failed.
- Tried with a different account with a good password and identical permissions…success
- Tried the changed one…failed.
- Fine…set it back to where it was – old password.
This was all for just one account…never even re-set it in the SQL Configuration Manager.
Then in testing, we found that any SQL Servers using that account were still up, but you could only connect by using RDP to the server, then SSMS. SSMS from the laptop failed with:
“The target principal name is incorrect. Cannot generate SSPI context”
Apparently, the account was either locked out from our failed logon attempts, or had been disabled in Active Directory due to its age. They do that sometimes. Most likely the issue was locked.
We restarted the SQL Server (O/S restart) and that resolved it once the AD group unlocked it.
My assumption is that the lockout either blocked Kerberos authentication due to SPN no longer being valid, or the SPN itself got corrupted. It was still there, just not working. Verified its existence through running SetSPN -L with the account name.
A pretty smart guy on Twitter in the #sqlhelp conversation I started seems to think it was simple DC connectivity. I’m not convinced, and we’ll never know for sure.
SQL Server did successfully register a new SPN on restart.
Hope this experience helps you troubleshoot something 🙂