Kerberos Authentication stopped working

  • Hi guys, i´ve only been a DBA for 2 years and still i feel like a rookie at this, need some help with kerberos not working.

    We had a service weekend this weekend, meaning we installed a boatload of windows fixes and restarted all our servers.

    after that one of our linked servers stopped working.

    its a double hop thing, user executes SP on server a, going though LS to server B and gathers data

    after looking into it, it seemed like my colleague had installed a developement instance in our test cluster using the same sql service account we use in a production instance, so... the SPNs where tied to 2 different sql server instances.

    i removed the SPN relating to the dev instance so now it looks like this:

    MSSQLSvc/SERVERNETWORKNAME.domain.com:<port from sql server log>

    MSSQLSvc/SERVERNETWORKNAME.domain.com:INSTANCENAME

    and this is how all my other instances look, that have a working kerberos authentication... but still it doesnt work

    Server A has been restarted after removing the bad SPNs and the linked server has been recreated (i read that somewhere)

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    gives the result "Kerberos" on both server A and server B

    still, the query:

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    select * from openquery(DWH, 'select auth_scheme from sys.dm_exec_connections where session_id=@@spid');

    is getting this result:

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Msg 4060, Level 11, State 1, Line 1

    Cannot open database "DATA" requested by the login. The login failed.

    I just dont see what i have missed, it all worked for the last year at least without a problem, the dev instance that used the prod instance service account is an old one, its been around for a loong time and although we had some issues with it before, we decided to change the service account on the dev instance now, so it doesnt have anything to do with the prod instance.

    any thoughts?

  • UPDATE:

    we found that the virtual networkname for the instance didnt have delegate trust for kerberos, so i enabled it, also we added the same SPNs as before but without .domain.com

    restarted the sql server service

    no change

    moved the instance to another sql server host in the cluster

    IT WORKS!

    so, it would seem that there is something fishy with the host...

    any ideas ?

  • Hi,

    Are your SQL Server services running under a domain account?

    If you have your SQL Services running under a domain account, you register the SPN's against those services. If the account has permissions to update AD, it'll do it automatically on SQL Server restart. It'll say in the SQL Server error log whether it was successful or not. Otherwise, you can use setspn to manually register them, e.g.

    setspn -S MSSQLSvc/servername.domain.com:INSTANCENAME domain\account

    setspn -S MSSQLSvc/servername.domain.com:PORTNUMBER domain\account

    You substitute the servername for whatever clients use to connect to your server. This can be the virtual name, or a DNS name for the virtual IP address.

    To get double-hop to work, in AD, find the account you are running the SQL Server services under. On the delegation tab, select 'Trust this user for delegation to specified services only', then 'Use Kerberos Only'. Select ADD, find the domain account the SPN's are registered against, then add the services you want to trust for delegation. That's pretty much all you have to do.

    It's a similar process if you're running the SQL Server services under a local machine account, except instead of doing these steps on the domain account, you do it on the machine account.

  • thanks for the reply

    i have done all this and it works on 3 out of 4 hosts in our cluster, if i move the instance to node 4 it stops working...

    any thoughts?

  • so do you have something like:

    MSSQLSvc/server1.domain.com:1333

    MSSQLSvc/server1.domain.com:INSTANCE1

    MSSQLSvc/server2.domain.com:1334

    MSSQLSvc/server2.domain.com:INSTANCE2

    MSSQLSvc/server3.domain.com:1335

    MSSQLSvc/server3.domain.com:INSTANCE3

    MSSQLSvc/server4.domain.com:1336

    MSSQLSvc/server4.domain.com:INSTANCE4

    MSSQLSvc/CLUST1SQL.domain.com:1333

    MSSQLSvc/CLUST2SQL.domain.com:1334

    MSSQLSvc/CLUST3SQL.domain.com:1335

    MSSQLSvc/CLUST4SQL.domain.com:1336

    MSSQLSvc/CLUST1SQL.domain.com:INSTANCE1

    MSSQLSvc/CLUST2SQL.domain.com:INSTANCE2

    MSSQLSvc/CLUST3SQL.domain.com:INSTANCE3

    MSSQLSvc/CLUST4SQL.domain.com:INSTANCE4

    then, in AD, all these services are trusted for delegation?

  • No, the clusternodes does not have a MSSQLSvc SPN to them.

    the 4 Cluster nodes all have:

    WSMAN/SERVER1

    WSMAN/SERVER1.domain.com

    MSServerClusterMgmtAPI/SERVER1.domain.com

    MSServerClusterMgmtAPI/SERVER1

    TERMSRV/SERVER1.domain.com

    TERMSRV/SERVER1

    HOST/SERVER1

    HOST/SERVER1.domain.com

    the instances all have:

    MSSQLSvc/VIRTUALSERVERNAME.domain.com:INSTANCENAME

    MSSQLSvc/VIRTUALSERVERNAME.domain.com:<dynamic port>

    MSSQLSvc/VIRTUALSERVERNAME:<dynamic port>

    MSSQLSvc/VIRTUALSERVERNAME:INSTANCENAME

    In AD, all the clusternodes, virtualservernames and SQL server service accounts are trusted delegation with kerberos

  • Can you check on the security settings set for linked server and provide the answer here? We recently faced the same issue when we changed the security option from use this login to be made using login's security context. All the cross server communications failed . We did an alternative to fix it and it's working fine now.

  • Hi,

    So, are you saying that the services are running under a local machine account? If not, the SPN's registered against the machine are irrelevant. If the services are running under a domain account, you need to look at the SPN's for that account.

    e.g.

    setspn -L mydomain\myserviceaccount

  • muthyala_51 (9/19/2013)


    Can you check on the security settings set for linked server and provide the answer here? We recently faced the same issue when we changed the security option from use this login to be made using login's security context. All the cross server communications failed . We did an alternative to fix it and it's working fine now.

    the linked server security setting is "Be made using the logins current security context"

  • adb2303 (9/19/2013)


    Hi,

    So, are you saying that the services are running under a local machine account? If not, the SPN's registered against the machine are irrelevant. If the services are running under a domain account, you need to look at the SPN's for that account.

    e.g.

    setspn -L mydomain\myserviceaccount

    i´m not sure i understand you, or you dont understand me =)

    all SQL Server instances have their own serviceaccounts in the AD

    setspn -l domain/serviceaccounts nets me this result (as previously posted):

    MSSQLSvc/VIRTUALSERVERNAME.domain.com:INSTANCENAME

    MSSQLSvc/VIRTUALSERVERNAME.domain.com:<dynamic port>

    MSSQLSvc/VIRTUALSERVERNAME:<dynamic port>

    MSSQLSvc/VIRTUALSERVERNAME:INSTANCENAME

  • a restart of the affected server solved the problem =/

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

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