Kerberos Delegation for linked server / specific authentification protocol

Question

Kerberos Delegation for linked server / specific authentification protocol

shp.shftr

SSC-Addicted

Points: 481
More actions
November 13, 2017 at 2:49 am

#333636

Hey there,
altough this is not a specific SQL Problem, I hope it nevertheless the correct category to place it in (if not, sorry!).
We got a problem in our infrastructure I'm struggling to solve by myself. I'm working in a MSSQL managed service department, therefor I am bound to preparations from other teams.
Our colleagues from active directory team set new security rules by which they are no longer allowed to set trust for kerberos to "all services" (which ran fine up until now for remote use of linked server to other SQL Servers -> double hops). With this new rule, I am not able to get remote use of linked server to work anymore.
-named instance is running with fixed port
-SPNs are set for both name and FQDN with named instance and port (see screenshot)
-service was added to delegation from ad Team (see screenshot)
Any ideas on how to solve this issue?
Thank you in advance! 🙂
Greetings from Germany,
Stefan
Edit: Just to make sure, every other login is working properly, the problem is only occuring when using a linked server remotely (double-hop). As I get an "anonymous login" error message, its clear that kerberos isn't working and SQL is trying to connect via NTLM.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Kerberos problems can be due to many different issues.

Did you check for duplicate SPNs?
DId you check the account itself in AD and make sure the account is not marked as sensitive and cannot be delegated?
Did you check sys.dm_exec_connections and check the auth_scheme to see if any of the connections are via Kerberos?
Did you check the Local Policies -> User Rights Assignment: Impersonate a client after authentication to make sure the service account has this right directly or via group membership?
Is TCP/IP enabled?

Sue

shp.shftr SSC-Addicted Points: 481 More actions · Answer 2

Hey Sue,

thanks for your answer:

Kerberos problems can be due to many different issues.
Did you check for duplicate SPNs?
> Yes, I double-checked that, no duplicated entrys for computer object or service account

DId you check the account itself in AD and make sure the account is not marked as sensitive and cannot be delegated?
> Yes, account can be delegated, option is disabled

Did you check sys.dm_exec_connections and check the auth_scheme to see if any of the connections are via Kerberos?
> All other connections are working fine with kerberos. Even the linked server is working when used on the system itself, but not when used remotely (double-hop)

Did you check the Local Policies -> User Rights Assignment: Impersonate a client after authentication to make sure the service account has this right directly or via group membership?
> Yes, the service account has all necessary permissions locally on the system

Is TCP/IP enabled?
> Yes, TCP/P is enabled

We have another instance on that same server which has the "old" option, remote-use of linked server are working fine there. So I guess it should be a problem with what to set in allowed authentification protocols.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

shp.shftr - Tuesday, November 14, 2017 12:31 AM
Hey Sue,
thanks for your answer:
Kerberos problems can be due to many different issues.
Did you check for duplicate SPNs?
> Yes, I double-checked that, no duplicated entrys for computer object or service account
DId you check the account itself in AD and make sure the account is not marked as sensitive and cannot be delegated?
> Yes, account can be delegated, option is disabled
Did you check sys.dm_exec_connections and check the auth_scheme to see if any of the connections are via Kerberos?
> All other connections are working fine with kerberos. Even the linked server is working when used on the system itself, but not when used remotely (double-hop)
Did you check the Local Policies -> User Rights Assignment: Impersonate a client after authentication to make sure the service account has this right directly or via group membership?
> Yes, the service account has all necessary permissions locally on the system
Is TCP/IP enabled?
> Yes, TCP/P is enabled
We have another instance on that same server which has the "old" option, remote-use of linked server are working fine there. So I guess it should be a problem with what to set in allowed authentification protocols.

What is the security configuration and mapping for the linked server? And could you please post the entire error message?
Try using klist to view the tickets, cache. Things like the server time being off a certain amount can affect kerberos.
Klist

Sue

shp.shftr SSC-Addicted Points: 481 More actions · Answer 4

Hi Sue,
I was finally able to get it to work.

I had to add the SPNs from the target instance as delegation services to the service account of the source instance.

Working fine now.

Thank you very much for your help and suggestions 🙂