Post reply

Kerberos

  • February 12, 2014 at 6:57 am

    #304137

    Hi,

    I'm using Windows Server 2012 R2 running SQL Server 2012 SP1 and despite setting my SPN for the service account I'm unable to get the connections to use Kerberos authentication, instead it just reverts back to NTLM.

    I wondered if anyone had any ideas?

    Thanks,

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 12, 2014 at 7:17 am

    #1688341

    Try using this tool to diagnose kerberos problems

    http://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=soc-n-

    -loc-[Services]-[farukc]

    If that doesn't work, can you post your SPN's here: setspn -L domain\serviceaccount

  • February 12, 2014 at 8:22 am

    #1688382

    Hi,

    Thanks for the link. It's useful tool. It is reporting that there are duplicates (see attachment). The first line is the server name, the second line is with the port number.

    However when I run setspn -L <machine name> I only see the two SQL SPN's plus some none SQL ones.

    Thanks,

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 12, 2014 at 8:52 am

    #1688410

    Is the service account Local System then?

  • February 12, 2014 at 9:05 am

    #1688424

    No, this is where I get's fun.

    It was running as local system, then we changed it to run as a domain account, the services started but when I tried to access it through SSMS I got the "sspi context" error so I then manually added the SPN and this then resolved the issue with connecting via SSMS, however now all connections are over NTLM and I cant figure out why Kerberos is not being used. It's obviously SPN related but I'm not sure how they are duplicate.

    If I run setspn <domain/username> then I get two records back, one with the port number and one without.

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 12, 2014 at 9:13 am

    #1688439

    Is it a default instance?

    If so, you should have the following SPN's only (against your domain account):

    MSSQLSvc/servername.fqdn:1433

    MSSQLSvc/servername.fqdn

    Delete any others you may have registered against the machine name and the domain account, using:

    setspn -D SPN domain\account

    setspn -D SPN machinename

    Alternatively, delete all of the SPN's registered for these SQL Server services, give the domain account the services are registered under 'read/write service principal names' permissions in AD, then bounce the SQL services (if you can). SQL will automatically register the SPN's itself.

  • February 12, 2014 at 9:45 am

    #1688470

    Hi,

    Got it, the account had the correct SPN's however the machine also had two additional SPN's (which should have been for the user).

    So setspn -L <user> was correct but setspn -L <computer> had entries it shouldn't have had - hence the duplicates.

    I wasn't able to remove duplicates these via the command line as it wants a username and these didn't have one as they were computer based however one of our infrastructure admin's has done it via the GUI and it all works perfectly now.

    Thanks a lot for your help, it's been a while since I've had to go over SPN issues so it's good to recap and learn a few things along the way.

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

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