SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Kerberos Authentication stopped working


Kerberos Authentication stopped working

Author
Message
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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?
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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 ?
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 3150
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.comTongueORTNUMBER 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.
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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?
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 3150
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?
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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
muth_51
muth_51
SSC Eights!
SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)

Group: General Forum Members
Points: 949 Visits: 2906
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.
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 3150
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
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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"
jCoke
jCoke
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 297
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search