Need to use Kerberos, but it still is using NTLM

  • I am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I STILL have problems...

    Here's our setup:

    SQL Server 2008 R2

    Named instance using a static port

    Windows 2008 R2.

    The server is is in DomainX.

    Our network admin manually registered the SPN for the DomainQ via this command:

    setspn -A MSSQLSvc/servername.DomainX:port# DomainQ\SQLID

    When I ran setspn -L DomainQ\SQLID, the spn was showing

    However, the sql log still showed an error that it couldn't register the SPN. When I checked sys.dm_exec_connections, it showed NTLM authentication.

    So then our network admin manually registered the SPN for the X domain via these 2commands:

    setspn -A MSSQLSvc/servername.domainX:port# DomainX\SQLID

    That gave a little bit of progress.

    The sql log showed that it successfully registered the Service Principal Name!! It showed the instance name instead of the port number. Not sure if that matters, but I took that as a good sign.

    However, when I checked sys.dm_exec_connections using SSMS, it showed NTLM authentication. (sigh)

    We also ran the following: setspn -A MSSQLSvc/servername:port# DomainX\SQLID

    Didn't help.

    I don't know if a server restart is needed, but I did. That didn't help. Plus, the service didn't automatically restart.

    I didn't see any obvious entries in the event viewer logs.

    What else do I need to try to get it to use Kerberos? Are there Active Directory settings for the SQLID that need to change? Are there Kerberos settings that need to change? Is there something in the registry?

    Any help would be appreciated.

  • Hi

    Are you running that query on the same box as SQL is installed? Try it on another box and see if you get the same effect. Have a look at this for extra troubleshooting tips.

    http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

    Regards

    Richard...

    http://www.linkedin.com/in/gbd77rc

  • Thanks for the reply.

    The link you listed is a good one and was another site I used as a reference.

    What we now have is that 1 out of 3 of our instances have the Kerberos auth.

    On the other two instances, the connections from the client machines of IT operations staff are Kerberos, and the connections from our application servers are NTLM.

    Due to time constraints, we've more or less given up on the Kerberos requirement. It bothers me that we weren't able to resolve it in full. Someday...

  • here are a couple of things that i've found when setting this up....

    1. Use FQDN's when specifying your service object e.g. setspn -A MSSQLSvc/Server_Name.sub_domain.root_domain.org.com:port domain\SQL_Service_Accont

    2. MSDTC Configuration

    On each SQL server being used for replication, perform the following steps from Start/Run dcomcnfg

    Expand the “Component Services” node, then the “My Computer” node, then right-Click and select “Properties”

    Select the MSDTC Tab, and select the “Security configuration…” button.

    Select Network DTC Access, Allow Remote Administration, Allow Inbound, Allow Outbound, Mutual Authentication Required.

  • MS DTC doesn't play into this, so that configuration is completely coincidental.

    How are the two domains related? Same forest? Different forests? Parent-child domain?

    K. Brian Kelley
    @kbriankelley

  • I wonder if this is because it is cached creds. Did you try to:

    1. Open cmd prompt

    2. type "klist.exe purge"

    3. Establish a new connection

    Additionally, you can install the Kerberos Configuration Tool: http://www.microsoft.com/en-us/download/details.aspx?id=39046. This will identify any issues with your SPNs and allow you to correct them.

    A server reboot would also flush the credential cache. Hope this helps!

    ~Slevin

  • Marcia Q (3/2/2011)


    I am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I STILL have problems...

    Here's our setup:

    SQL Server 2008 R2

    Named instance using a static port

    Windows 2008 R2.

    The server is is in DomainX.

    Our network admin manually registered the SPN for the DomainQ via this command:

    setspn -A MSSQLSvc/servername.DomainX:port# DomainQ\SQLID

    When I ran setspn -L DomainQ\SQLID, the spn was showing

    However, the sql log still showed an error that it couldn't register the SPN. When I checked sys.dm_exec_connections, it showed NTLM authentication.

    So then our network admin manually registered the SPN for the X domain via these 2commands:

    setspn -A MSSQLSvc/servername.domainX:port# DomainX\SQLID

    That gave a little bit of progress.

    The sql log showed that it successfully registered the Service Principal Name!! It showed the instance name instead of the port number. Not sure if that matters, but I took that as a good sign.

    However, when I checked sys.dm_exec_connections using SSMS, it showed NTLM authentication. (sigh)

    We also ran the following: setspn -A MSSQLSvc/servername:port# DomainX\SQLID

    Didn't help.

    I don't know if a server restart is needed, but I did. That didn't help. Plus, the service didn't automatically restart.

    I didn't see any obvious entries in the event viewer logs.

    What else do I need to try to get it to use Kerberos? Are there Active Directory settings for the SQLID that need to change? Are there Kerberos settings that need to change? Is there something in the registry?

    Any help would be appreciated.

    use the latest version of SETSPN which you can get from any Windows 2008 R2 server with the AD domain services role installed. The latest SETSPN has a -S switch which checks for duplicates. Your AD admin must also enable for delegation the user account under which sql runs and ultimately the SPN is created against.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Actually, you don't need to enable delegation unless you are working with linked servers and windows authentication. The account does need the permission to write public information in active directory in order to create the SPNs.

    Slevin

  • Is your service account in DomainQ or DomainX? Everything has to be in the same domain for Kerberos to work including the user connecting.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Don't use the instance name in the SETSPN command. Just make sure the machine name (NETBIOS and FQDN) and port is correct.

    This part was giving me some headaches too in the past.

    __________________
    MS-SQL / SSIS / SSRS junkie
    Visit my blog at dba60k.net

  • Marcia Q - Did you get it working? I just with through the same setup on my end and I can hopefully help. Just wanted to check with you in care you already solved it.

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

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