1

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

Improve this question
16
  • What does the SQL Server log show for the failed logins?
    – David Browne - Microsoft
    Dec 17, 2018 at 15:38
  • And have you tried connecting with the IP Address? Fully-Qualified Domain Name? And is the Server's hostname in DNS the same as its computer name?
    – David Browne - Microsoft
    Dec 17, 2018 at 15:48
  • @DavidBrowne-Microsoft IP, and DNS none works. still the same error. I have zero errors regarding logins showing on either SQL logs or windows event viewer. the error only show client side. Also running Kerberos config manager creates an empty log file.
    – Franck
    Dec 17, 2018 at 15:53
  • Does the SQL Server error log show anything related to Kerberos or SSPI ?
    – Hannah Vernon ♦
    Dec 17, 2018 at 16:12
  • @MaxVernon The ONLY logs i have in SQL are transactional backup every 15 minutes. Literally nothing else. Actually as far as i can go back i have only that until the log about the last reboot of the server about 2 hours ago.
    – Franck
    Dec 17, 2018 at 16:13

1 Answer 1

Reset to default
0

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 LocalSystem and both SPN were 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.