Configuring Kerberos Authentication

  • Rob Symonds

    SSCrazy

    Points: 2145

    Brian,

    I run all SQL services under one domain account. When I do SETSPN -L on that account, I don't see any SPNs listed. (I have never used SETSPN to manage SPNs.)

    I have SQL 2005 on a couple of different servers. When I login to SQL using Windows Auth through Management Studio over TCP/IP it works on all servers except one. This means they are defaulting to NTLM?

    On the server I cannot login to, the login over TCP/IP fails with:

    Cannot connect to SQL1.

    Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

    I can login using Named Pipes no problem. What could be wrong with this server that is preventing me from logging in over TCP/IP?

    Also, if I'm using one domain account for multiple SQL Servers, can I just keep adding SPNs to that account?

    Thanks!

    Rob

  • Rob Symonds

    SSCrazy

    Points: 2145

    I should've checked before I posted: I can see using the query from the article that all the logins are using NTLM.

    I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Rob Symonds (12/15/2008)


    I should've checked before I posted: I can see using the query from the article that all the logins are using NTLM.

    I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?

    There are quite a few reasons. One of the most common is that an SPN did get registered, but to a different account. Here's how to look for that:

    Service Logons Fail Due to Incorrectly Set SPNs

    Look for the section on how to use LDP to find duplicate SPNs. A duplicate or incorrect SPN will definitely cause that failure. Otherwise, if you go to support.microsoft.com and search for "cannot generate SSPI context" you'll get a handful of articles that talk about the issues.

    And with respect to one domain account for multiple SQL Servers, adding the additional SPNs is fine and everything will work.

    K. Brian Kelley
    @kbriankelley

  • Rob Symonds

    SSCrazy

    Points: 2145

    Thanks Brian. The MS docs on SPNs are confusing.

    I'm looking at things with our AD admin. More investigation reveals the following:

    All services for SQL Server run under the CORP\SqlServices domain account. SETSPN -L CORP\SqlServices shows no SPNs attached.

    However, SETSPN -L SQL1 (where SQL1 is the name of my SQL box) shows the MSSQLSvc SPN:

    HOST/SQL1$.CORP

    HOST/SQL1$

    MSSQLSvc/SQL1.CORP.[MYCOMPANY].COM:1433

    HOST/SQL1.CORP.[MYCOMPANY].COM

    HOST/SQL1

    SETSPN -L SQL2, SQL3, etc (where SQL2, SQL3 are the names of other SQL boxes) shows no SPNs:

    MSSQLSvc/SQL2.CORP.[MYCOMPANY].COM:1433

    HOST/SQL2.CORP.[MYCOMPANY].COM

    HOST/SQL2

    MSSQLSvc/SQL3.CORP.[MYCOMPANY].COM:1433

    HOST/SQL3.CORP.[MYCOMPANY].COM

    HOST/SQL3

    We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.

    --

    A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?

    Thanks again!

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Rob Symonds (12/15/2008)


    We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.

    --

    A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?

    Thanks again!

    Is SQL1 running under the local System or Network Service account?

    K. Brian Kelley
    @kbriankelley

  • Rob Symonds

    SSCrazy

    Points: 2145

    Is SQL1 running under the local System or Network Service account?

    SQL1 is running under the domain account: CORP\SqlServices. But what I'm thinking is that when the last DBA installed it last year, he installed it (or maybe upgraded it) under a local account? I don't have any way of knowing. I wasn't here so I don't have a history on the box. I just inherited it with the problem.

    Thanks.

  • Rob Symonds

    SSCrazy

    Points: 2145

    Update: problem resolved.

    We kept trying to delete that SPN but didn't have the right syntax. The AD admin got it to work though with the following:

    setspn -D MSSQLSvc/SQL1.CORP.[MYCOMPANY].com:1433 SQL1

    Thanks for the great article and forum replies Brian. MS has done a lame job with the documentation and tools for this. It's good to have somebody in the community like you that can help make sense of it all.

  • SA-1

    SSCrazy Eights

    Points: 8113

    The article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.

    I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.

  • Maxer

    SSCrazy Eights

    Points: 8948

    SA (1/15/2009)


    The article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.

    I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.

    I would be very interested in knowing more about that as well.

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    The SPN does not need to be recreated when SQL Server is stopped and restarted. The only reason this would be necessary is the SQL Server is using dynamic ports and it has a port change. If SQL Server is configured to use static ports (which it should), then the SPN only has to be registered once.

    K. Brian Kelley
    @kbriankelley

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4142

    Hi Brian, Great article I agree.

    I am having trouble getting Kerberos set up tho.

    I am using:

    - a Virtual Machine (VMWare)

    - sql 2005 SP3

    I have run the 4 steps adived:

    SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService

    now when I run SETSPN - L MyDBServer I get:

    Host/MyDBServer

    HOST/MyDBServer.mydomain.com

    and for SETSPN - L MyDomain\SQLServerService I get:

    MSSQLSvc/MyDBServer.mydomain.com

    MSSQLSvc/MyDBServer.mydomain.com:1433

    MSSQLSvc/MyDBServer

    MSSQLSvc/MyDBServer:1433

    I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?

    I can see that sessions are still using NTLM, any ideas as to what is nobbling me?

    Thanks,

    Carlton..

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Carlton Leach (4/29/2009)


    Hi Brian, Great article I agree.

    I am having trouble getting Kerberos set up tho.

    I am using:

    - a Virtual Machine (VMWare)

    - sql 2005 SP3

    I have run the 4 steps adived:

    SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService

    now when I run SETSPN - L MyDBServer I get:

    Host/MyDBServer

    HOST/MyDBServer.mydomain.com

    and for SETSPN - L MyDomain\SQLServerService I get:

    MSSQLSvc/MyDBServer.mydomain.com

    MSSQLSvc/MyDBServer.mydomain.com:1433

    MSSQLSvc/MyDBServer

    MSSQLSvc/MyDBServer:1433

    I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?

    I can see that sessions are still using NTLM, any ideas as to what is nobbling me?

    Thanks,

    Carlton..

    The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.

    Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.

    K. Brian Kelley
    @kbriankelley

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4142

    K. Brian Kelley (4/30/2009)


    Carlton Leach (4/29/2009)


    Hi Brian, Great article I agree.

    I am having trouble getting Kerberos set up tho.

    I am using:

    - a Virtual Machine (VMWare)

    - sql 2005 SP3

    I have run the 4 steps adived:

    SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService

    SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService

    now when I run SETSPN - L MyDBServer I get:

    Host/MyDBServer

    HOST/MyDBServer.mydomain.com

    and for SETSPN - L MyDomain\SQLServerService I get:

    MSSQLSvc/MyDBServer.mydomain.com

    MSSQLSvc/MyDBServer.mydomain.com:1433

    MSSQLSvc/MyDBServer

    MSSQLSvc/MyDBServer:1433

    I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?

    I can see that sessions are still using NTLM, any ideas as to what is nobbling me?

    Thanks,

    Carlton..

    The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.

    Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.

    Hi Brian,

    Thanks for the swift response, Yes this is a default SQL instance listening on port 1433.

    And yes the clients are in the same domain (I am one of them).

    I am waiting for windows lot to come back to me with what type of domain (2k or 2k03 etc) but I don't think this is going to matter.

    I have also asked if there is a specific setting (possibly group policy) that is going to prevent authentication via kerberos.

    One question: Are these SPN's going to have any kind of detrimental affect on anything if I do not remove them?

    Thanks again,

    Carlton..

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    They should not have a detrimental effect if you remove them. You might use kerbtray to see if the tickets are even being created/assigned.

    K. Brian Kelley
    @kbriankelley

  • SQL Thetan

    Say Hey Kid

    Points: 664

    Great article!

    Any chance of another great article for Kerberos Delegation?

Viewing 15 posts - 46 through 60 (of 89 total)

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