Kerberos and NTLM

• TryingToLearn

  SSCrazy

  Points: 2645

  More actions

  July 12, 2017 at 1:19 pm

  #338851

  We use NTLM authentication in our environment.

  SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;
  Returns NTLM from all my instances

  How do you properly convert SQL Server  from NTLM to Kerberos? and what are the impacts?

  and how does a sql credential connection fit in here? 

  Thanks

• Super Cat

  SSCertifiable

  Points: 7332

  More actions

  July 13, 2017 at 4:40 am

  #1950712

  I think that's for the WINDOWS specialist NOT a SQL DBA.
  This is how they are authenticating around the domain NOT SQL specific.

  As far as I am aware this will have impact across the domain not just SQL SERVER.

• TryingToLearn

  SSCrazy

  Points: 2645

  More actions

  July 13, 2017 at 4:53 am

  #1950713

  Interesting, Yes, we are looking to upgrade the entire domain. So there no setting/config in SQL to determine authenticating protocol method? Even when using SQL logins?
  Does this mean the window users protocol is determined when they log into Windows or the app and that protocol is then passed to SQL?

  Thanks
   

• Super Cat

  SSCertifiable

  Points: 7332

  More actions

  July 13, 2017 at 7:52 am

  #1950741

  I believe the protocol is setup @the domain level not per account.
  KERBEROS is the preferred method, Some forget to set up DELGATION AND SPNs correctly when opting for KERBEROS.
  Make sure this is correct if KERBEROS is chosen for the new domain. Not my area of expertise I just know it needs to be done or it can cause Authentication issues especially when credentials have to hop a couple of servers.i

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 13, 2017 at 8:29 am

  #1950754

  TryingToLearn - Thursday, July 13, 2017 4:53 AM

  Interesting, Yes, we are looking to upgrade the entire domain. So there no setting/config in SQL to determine authenticating protocol method? Even when using SQL logins?
  Does this mean the window users protocol is determined when they log into Windows or the app and that protocol is then passed to SQL?

  Thanks
   

  It is however the application tries to perform the authentication against active directory. Kerberos will be tried first and it will fallback to NTLM if Kerberos fails.
  You could be connected to two different SQL Servers - one with Kerberos authentication and one with NTLM.
  You can enable Kerberos with different applications but generally they will still have way to fallback to NTLM if needed. So it's more that there can be settings or configs to enable Kerberos rather than determine it. You determine the authentication type after the fact - just as you did for SQL Server in your original query. 

  This link is to an old article but much of it still applies and it seems to be addressing a lot of what you are trying to understand:
  Understanding Kerberos and NTLM authentication in SQL Server Connections

  Sue