locked
Problem getting Kerberos connection RRS feed

  • Question

  • Hi,

    Trying to setup kerberos so that new connections from my local PC to the remote sql server show as kerberos with the following code.

    select net_transport, auth_scheme from master.sys.dm_exec_connections where session_id = @@Spid

    It's shows as NTLM when i make a new connection at the server using TCP. 

    I've checked with the AD guys and they have assured me that the SQL service account is definately selected as 'Trust This user for delegation to any service'.

    Looking through other posts i've added the following SPN Names when running the following command setspn -L infosqlexec :-

    mssqlsvc/plato:49728

    mssqlsvc/plato:xuhb.nhs.uk:49728

    My SQL Server is definately running under uhb\infosqlexec as the service account. The account is not an administrator.

    The service is a named instance on port 49728 There is a totally seperate instance on port 50044 which im not concentrating on at the moment.

    When i try running the sql above i'm still showing as connected via NTLM. This is driving me mad. Is there anything else that i'm not doing? I eventually need the kerberos connection to run BULK INSERT from a network share importing daily feeds.

    But i still can't get this initial step working.

    Anyone have any ideas on what i can try? After adding the SPNs is there something i need to do before it will pick up? (At the moment i'm creating a new connection to test it)

    Thanks in advance

    Ian.

    Friday, January 31, 2014 10:54 AM

All replies

  • Hello Ian,

    There is a free tool Microsoft® Kerberos Configuration Manager for SQL Server® available, which you can use to check & fix your configuration.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, January 31, 2014 11:02 AM
  • Hi,

    The "Trust this user for delegation to any service" bit is only useful for when you want to double hop.  Out of interest, ask your AD guys to select specific services, then try and select the services registered against your service account.  Do they appear in the list?

    Is the SQL Server a named instance?  You should have the following SPN's:

    mssqlsvc/plato:xuhb.nhs.uk:49728

    mssqlsvc/plato:xuhb.nhs.uk:INSTANCENAME

    so, I'd delete mssqlsvc/plato:49728, using setspn -d mssqlsvc/plato:49728 mydomain\infosqlexec and register a new one with the instance name.

    Alternatively, delete the SPN's completely, give the infosqlexec account permissions to read/write service principal names in AD, then recycle the SQL Server services.  It'll do it itself then.


    Thanks, Andrew
    My blog...

    Friday, January 31, 2014 11:14 AM
  • Thanks Andrew,

    Changing to the two SPNs you suggested seems to have done the trick. I'm now getting KERBEROS connections.

    Thank you again

    Ian.

    Friday, January 31, 2014 11:38 AM
  • Sorry Andrew one last thing.

    That all works, can do the bulkinsert and linked server via 'currently logged on user' all great on instance 1.

    I tried setting the 2nd instance up using the same details except changing the port and instance name but am still getting NTLM connection on 2nd instance. Both instances are using the same domain user.

    Any ideas?

    Friday, January 31, 2014 11:48 AM
  • Hi,

    Run SPN script to check for duplicate SPN. iIf you find there is duplicate SPN, remove the duplicate SPN. For more information, see:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4b1a8a6-2d06-461b-a6f2-4121eb34c1f4/question-of-database-serverfor-one-user-can-use-impersonate-with-delegation-of-kerberos-but-the?forum=sqlintegrationservices

    Thanks.


    Tracy Cai
    TechNet Community Support

    Monday, February 3, 2014 7:36 AM
  • Hi,

    Sorry for the late reply.  Did you get anywhere?

    Can you post an example of the SPN's you have for the second instance?


    Thanks, Andrew
    My blog...

    Tuesday, February 4, 2014 11:02 AM
  • Tuesday, February 4, 2014 12:03 PM
  • Hi, The only thing I can see here is that the working instance has "." separator, whereas you have a colon separator between plato and xuhb for the non-working instance. Can you confirm?

    Thanks, Andrew
    My blog...

    Tuesday, February 4, 2014 1:20 PM