We are trying to figure out what is wrong with our server. Right now i have many accounts that are sql accounts and windows authenticated (domain accounts).
When i connect with ANY windows authenticated account i get an error
Cannot generate SSPI context
The account is good. I have also been trying to connect simply using Management Studio and i get the same error. If i use any SQL accounts it works and all windows authenticated account fails.
That being said, if i do a remote desktop to the SQL server itself and try using Management Studio on the server itself, all Windows authenticated account works without error.
The error only shows for remote computer (same domain / ip range) when using windows authenticated connections.
i have found this but it doesn't fix anything. According to the doc no accounts should work if we are getting this error.
Now what changed, the only thing that change is that the server was a virtual machine on VMWare, there was a conversion done to switch it to hypervisor and the instance has been restarted.
Anyone else would know when SSPI error can show based of where the authentication request was called ? i haven't been able to find anything. Could possibly be a bad error. Our servers have been down for the whole weekend we still haven't found anyone with the same issue.
Important notes :
– PC that remote and connect to SQL are in the same domain, same physical location, plugged in the same rack mount network wise.
– Running using IP or DNS doesn't change anything.
– no error logs show up in SQL and neither in Event Viewer on the server
Best Answer
We finally found how to fix the issue. Unfortunately we do not know why it fixes it as the old VMWare image have the same setting it has on the Hyper-V image. Both suddenly stopped working.
To fix it we had to change the SQL server instance that has been running on the main Domain admin account for the last 10 years to run on
LocalSystemand bothSPNwere registered under a user account. That user was another domain admin account which was the one originally used when SQL was first installed 10 years ago. That account is still active and used. We removed the SPN from the account and put them directly under the SQL dns.Note that i don't know that interface where SPN are listed but i understood that it list all computer DNS names and all user account of the domain and SPN can be under either. Both IT have no idea why this suddenly stopped working. According to oldest image backup of the servers those settings never changed but that's all we needed to do to make everything work again.