Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Kerberos Authentication stopped working Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 1:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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?
Post #1495785
Posted Wednesday, September 18, 2013 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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 ?
Post #1495834
Posted Wednesday, September 18, 2013 7:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:28 AM
Points: 858, Visits: 2,512
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.
Post #1495926
Posted Wednesday, September 18, 2013 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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?
Post #1495928
Posted Wednesday, September 18, 2013 8:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:28 AM
Points: 858, Visits: 2,512
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?
Post #1495939
Posted Thursday, September 19, 2013 4:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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
Post #1496300
Posted Thursday, September 19, 2013 11:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 9:19 AM
Points: 420, Visits: 2,126
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.
Post #1496504
Posted Thursday, September 19, 2013 2:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:28 AM
Points: 858, Visits: 2,512
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
Post #1496584
Posted Wednesday, September 25, 2013 5:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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"
Post #1498265
Posted Wednesday, September 25, 2013 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:20 AM
Points: 34, Visits: 217
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

Post #1498266
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse