locked
cannot generate sspi context error on SQL 2016 instance RRS feed

  • Question

  • Getting cannot generate sspi context error, new dev server build windows standard 2016, default instance is sql standard 2016, named instance sql standard 2014, firewall opened up but getting this error when I try to connect remotely to the 2016 default instance, I do not have any problems connecting to the 2014 instance. Both instances are running with the same domain user account which was assigned during the sql installs. We are in the process of upgrading some sql 2008 R2 instances to 2014 then to 2016 and I hit this during the server build. I believe it is a kerberos issue but wondering why I am hitting it on one instance (2016) instead of both instances (2014 and 2016)?  I have not tried creating the SPN for the service yet as I would like to know why this is happening, does it have something to do with enhanced security on 2016? I am also see events 455/489 and 490 in the event log on the machine "sqlservr (4164) Error -1032 (0xfffffbf8) occurred while opening logfile C:\Windows\system32\LogFiles\Sum\Api.log."

    Thanks

     
    • Edited by Abeljdang Tuesday, January 14, 2020 4:57 PM details
    Tuesday, January 14, 2020 4:39 PM

Answers

  • I had set up another server, windows 2016 and SQL 2014 default instance and could not connect remotely but used the kerberos config manager to set the spn, and this resolved the issue. I was never able to resolve the issue with the other server which had windows 2016, sql 2016 default and sql 2014 instance where I could connect remotely (ntlm) to the instance but not to the default instance (sspi error). The network admin had been on configuring other components (not sql) and it is now allowing ntlm connections.
    • Marked as answer by Abeljdang Wednesday, January 29, 2020 8:39 PM
    Wednesday, January 29, 2020 8:39 PM

All replies

  • Hello,

    “cannot generate sspi context error” is a quite common error with a variety of causes. It is hard to tell you specific reason. However you are right, it is main a Kerberos issue. And there is  a tool ‘Kerberos Configuration Manager’ which can resolve this error. Here is download linkage: https://support.microsoft.com/en-nz/help/2985455/kerberos-configuration-manager-for-sql-server-is-available 

    If you want to troubleshooting manually, you have to verify the domain, SPNs, server and client environment and so on.You can follow the steps of resolving this problem in this article: How to troubleshoot the "Cannot generate SSPI context" errormessage 

    Hope it will help. Any further question, let’s me know please.

    Best Regards!

    Dawn Yang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 15, 2020 6:28 AM
  • Thanks for the info Dawn, I had come across these links and have run the kerberos configuration manager to create the script but I was trying to figure out the difference in the 2 instances as to why it works on one and not the other using the same AD account? It appears to be something with SQL 2016  as the 2 instances (the 2014 and 2016) on the same server were installed/configured the same.
    Wednesday, January 15, 2020 3:19 PM
  • Maybe you can run Kerberos configuration manager on both instances to diagnose the reason for the error. You can read log file in %APPDATA%\Microsoft\KerberosConfigMgr

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 16, 2020 5:34 AM
  • I tried running it on both instances and created the setspn commands. for the instance I can't connect to the status was "Misplaced", for the other instance which I can connect to the status is "Missing". I tried running the setspn command but it failed with a couple errors, insufficient rights to perform this action and duplicate spn. I tried running setspn -X to see what duplicates we had but it reported no duplicates for the domain.


    Thursday, January 16, 2020 4:43 PM
  • Hello,

    Sorry for the delaying reply.

    As you mentioned, i think there are some different configuration between instance 2014 and 2016.

    Could you post the error message so that we can analysis the reason for specific error? 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 23, 2020 1:28 AM
  • When I try to connect through SSMS it immediately pops up a window "cannot connect to serverxx.  Additional information: the target principal name is incorrect. cannot generate sspi context.(microsoft sql serbver)"
    Thursday, January 23, 2020 3:10 PM
  • I had set up another server, windows 2016 and SQL 2014 default instance and could not connect remotely but used the kerberos config manager to set the spn, and this resolved the issue. I was never able to resolve the issue with the other server which had windows 2016, sql 2016 default and sql 2014 instance where I could connect remotely (ntlm) to the instance but not to the default instance (sspi error). The network admin had been on configuring other components (not sql) and it is now allowing ntlm connections.
    • Marked as answer by Abeljdang Wednesday, January 29, 2020 8:39 PM
    Wednesday, January 29, 2020 8:39 PM