September 18, 2013 at 1:44 am
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?
September 18, 2013 at 4:34 am
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 ?
September 18, 2013 at 7:43 am
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.
September 18, 2013 at 7:49 am
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?
September 18, 2013 at 8:23 am
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?
September 19, 2013 at 4:38 am
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
September 19, 2013 at 11:16 am
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.
September 19, 2013 at 2:17 pm
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
September 25, 2013 at 5:29 am
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"
September 25, 2013 at 5:33 am
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
October 22, 2013 at 4:44 am
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