locked
change NTLM to KERBEROS sql 2008 r2 RRS feed

  • Question

  • how do you change the auth scheme for sql server from NTLM to KERBEROS?

    There are two servers, A and B.

    Running the following query on serverA gives "NTLM"  and "KERBEROS" on ServerB

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    What I want to achieve is create a link between A and B using a Domain account.

    Thanks

    Tuesday, November 6, 2012 5:27 PM

Answers

  • The connection always attempts Kerberos first, and if it fails it tries NTLM. So there isn't a setting for it. As Andrew is saying, something is not working for Kerberos with server A. In addition to Andrew's advice, check the Windows event log and SQL Server error log on the SQL Server computer. My guess is that the user account that is used by the Database Engine doesn't have permission to set the SPN. More info is at Register a Service Principal Name for Kerberos Connections http://msdn.microsoft.com/en-us/library/ms191153.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Shulei Chen Wednesday, November 7, 2012 3:20 AM
    • Marked as answer by Shulei Chen Wednesday, November 14, 2012 7:25 AM
    Tuesday, November 6, 2012 9:37 PM
  • Hi,

    Is there an SPN registered for the SQL Server services against the account running them on Server A?

    setspn -L mydomain\myserviceaccount

    Are ServerA and ServerB are in the same domain?  Are you using TCP/IP?

    If you want to bridge the connection from ServerA to ServerB from a 3rd server/client, then you'll need to allow enable kerberos delegation in AD for the service account.  This will allow a double-hop...



    Thanks, Andrew


    Tuesday, November 6, 2012 5:31 PM

All replies

  • Hi,

    Is there an SPN registered for the SQL Server services against the account running them on Server A?

    setspn -L mydomain\myserviceaccount

    Are ServerA and ServerB are in the same domain?  Are you using TCP/IP?

    If you want to bridge the connection from ServerA to ServerB from a 3rd server/client, then you'll need to allow enable kerberos delegation in AD for the service account.  This will allow a double-hop...



    Thanks, Andrew


    Tuesday, November 6, 2012 5:31 PM
  • The connection always attempts Kerberos first, and if it fails it tries NTLM. So there isn't a setting for it. As Andrew is saying, something is not working for Kerberos with server A. In addition to Andrew's advice, check the Windows event log and SQL Server error log on the SQL Server computer. My guess is that the user account that is used by the Database Engine doesn't have permission to set the SPN. More info is at Register a Service Principal Name for Kerberos Connections http://msdn.microsoft.com/en-us/library/ms191153.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Shulei Chen Wednesday, November 7, 2012 3:20 AM
    • Marked as answer by Shulei Chen Wednesday, November 14, 2012 7:25 AM
    Tuesday, November 6, 2012 9:37 PM