locked
NTLM is not working for Domain Accounts on the Default Instance RRS feed

  • Question

  • I have 3 SQL 2016 instances recently built on a WS 2016. The SQL Services run under domain accounts which are different for each instance. For all instances, SPNs are not registered during start up, KERBEROS is not expected/required and connections are made in NTLM.

    From another server (same domain), Windows and SQL connections are successful for the 2 named instances (under NTLM).
    However, for the default instance we get the error: "Cannot generate SSPI context" when trying to connect with a Windows connection. SQL connections are OK.

    Please help.

    Tuesday, February 26, 2019 1:31 AM

Answers

  • Hi ClaudioLGatto,

     

    According to your description, your two named instances can connect successfully using NTLM, but your default instance cannot be rolled back to NTLM authentication. Would you please tell me if you have modified the service account of the default instance?

     

    Actually, remote connections are generally preferred to use Kerberos authentication. If no such SPN is registered in AD, NTLM authentication will be used.  If the spn registration error or multiple accounts have registered this spn, then an error will be reported and will not be rolled back to ntml authentication.I suggest you use Kerberos Configuration Manager for the default instance to check if the wrong spn has been commented The download link :https://www.microsoft.com/en-us/download/details.aspx?id=39046

     

    As a solution, if you find the wrong SPN you can delete it directly, sql server will use NTLM authentication.

     

    Best regards,

    Dedmon Dai


    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

    Tuesday, February 26, 2019 3:29 AM

All replies

  • Hi ClaudioLGatto,

     

    According to your description, your two named instances can connect successfully using NTLM, but your default instance cannot be rolled back to NTLM authentication. Would you please tell me if you have modified the service account of the default instance?

     

    Actually, remote connections are generally preferred to use Kerberos authentication. If no such SPN is registered in AD, NTLM authentication will be used.  If the spn registration error or multiple accounts have registered this spn, then an error will be reported and will not be rolled back to ntml authentication.I suggest you use Kerberos Configuration Manager for the default instance to check if the wrong spn has been commented The download link :https://www.microsoft.com/en-us/download/details.aspx?id=39046

     

    As a solution, if you find the wrong SPN you can delete it directly, sql server will use NTLM authentication.

     

    Best regards,

    Dedmon Dai


    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

    Tuesday, February 26, 2019 3:29 AM
  • Hi Dedmon,
    We switched the service account to Local System, the connections were successful.
    (SQL Error log showed that SPNs were successfully registered).

    We then switched to a new domain account but received the same error.
    (SQL Error log showed that SPNs were not successfully registered).

    Thank you for the download link, it will help with further troubleshooting.

    Thanks & Regards
    Claude

    Tuesday, February 26, 2019 10:08 PM
  • Hi Claude,

     

    I'm glad to know that your problem has been solved. In order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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, February 27, 2019 1:23 AM