Question about SPN and kerberos vs NTLM

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    Recently I updated some virtual accounts to AD accounts and in some cases (not all) i ran into the login failure:

    The target principal name is incorrect.  Cannot generate SSPI context.

    So we deleted the SPN's that were in AD under the computer account and recreated them under the service account.

    On another server i now get the error at startup that it cant create the SPN but whatever i can still login.

    Now i'm looking at the auth_scheme on 38 servers and in 8 cases its Kerberos and the other 30 are NTLM.

    My question is what should it be? Why in one case am i not able to login until the SPN's are fixed but in other cases it doesn't seem to matter?

  • Sue_H

    SSC Guru

    Points: 90287

    It doesn't necessarily need to be one or the other. Kerberos would be preferred but the connections should fall back to NTLM if Kerberos fails. If on start up it can't created the SPN, the service account likely doesn't have the permissions to do so. And if it attempted to create the SPNs after they were already created it could be that the SPNs aren't configured correctly. So a few different issues. And yes, it can be a painful headache. At this point, you might want to try the Kerberos Configuration Manager for SQL Server- if nothing else to check the configurations

    https://www.microsoft.com/en-us/download/details.aspx?id=39046

     

    Sue

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    I've been running setspn -q and -l to see what we have.

    I'll install the kerberos config mgr.

    Thanks for the info!

     

  • Andrey

    Say Hey Kid

    Points: 684

    hi

    NTLM will be used for local connections via shared memory

    KERBEROS is for  TCP connections

    at least I haven't seen the opposite for years

     

    check:

    select net_transport,auth_scheme,count(1) [sessions]
    from sys.dm_exec_connections
    group by net_transport,auth_scheme
  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    if there are no SPNs it will always be NTLM regardless of how you connect, local or remote.

    You only get a kerb connection if a valid SPN exists.

    Right now when i connect to 40 servers and check auth_scheme on all of them i only get Kerberos on about 10 of them. Those 10 all have SPNs setup for the service account or use Local system accounts (those also have SPNs that get created automatically) and the other 30 are NTLM.

    After being hip deep in this SPN Kerberos stuff i'm finally understanding it. I even solved the double hop issue today. Unfortunately when you impersonate it wont use (current security context) you still need to map the user. Ugh

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply