Linked Server using login current security context with Kerberos Delegation enabled on any services onto domain service user name does not work until...

Question

Hello,

I've set up 2 SQL Server Failover Cluster Instances (no AOAG).
I gave all full control permission to CNO of nodes and clustername each other, I did the same for both Virtual Network Names of the two instance.

I used a domain user to startup SQL Engine for both of the instances (same goes for the agent), the user is the same for both.

Cluster is Active-Active, therefore one instance has to be hosted in one node, and use the other node, who hosts the other instance, as passive.

Kerberos delegation has been set on the startup domain user for any services (Kerberos only) .

Linked server is created with SQLNativeClient11 provider, uses current login security context, and points from one instance to another (same cluster).

SPN and delegation (checked also from Kerberos Configuration manager, and SQL error logs) are registered successfully, but here's what happens.

If both of them uses same cluster node (just for test), linked server works just fine with every domain user who has a domain login (personal) on both instance, that is our expected outcome.

However when an instance has been moved to the other, to enable active active cluster configuration, linked server stops working, giving NT Anonymous\Logon error.

This happens at first, until we open a session (tested with SSMS) with the login of startup domain user of SQL engines.

After that session has been opened, linked server begin to work fine even with other domain users.

A workaround would be easy, however does anyone knows the root cause of the issue? Am I missing some permission from active directory on CNO too?

Please let me know.

EDIT 1: I posted another comment trying to explain better the situation and providing the results of our simulation:

Hi all,

thanks for your answers, however I did not say that linked server using kerberos delegation does not work at all.

Both instance are running in a Windows Server Failover Cluster, except that they run usually in different nodes.

Also client connection does happen on same domain, since our test was to test connection from linked server through SSMS installed in both cluster nodes.

Therefore links you provided are answers to what it looks like a generic linked server that uses kerberos token to authenticate.

The situation I'm describing is the following:

Two cluster nodes C1 and C2 joined the same domain.

Let's say instances A and B are running in C1 (using WSFC not AOAG). If I use linked server set up in instance A that points to B, it works but I guess it's using a NTLM token.

When B runs on C2 (as it has requested to be by our customer), client connection now has to double hop, therefore it tries to use Kerberos token. But it does not work until a simple session is

opened by service user (the domain user created to run sql server engine and used also for agent).

After the mentioned session is successfull, suddenly all other logins (belonging to same domain as cluster, service users and client) can use kerberos delegation.

If that session has not been opened, it returns NT Anonymous\logon.

I hope to hear about you soon.

Answer 1

Hi @MANGIALARDI Domenico (NPO) ,

Welcome to Microsoft Q&A!
Please read this artcle:
https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/
Hope it would bring your attention to some points you hadn't noticed before.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Thank you for posting your query.

Kindly follow the steps provided below to resolve your issue.

To use Kerberos authentication with SQL Server requires both the following conditions to be true:

The client and server computers must be part of the same Windows domain, or in trusted domains.

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it's registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn't been performed or fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication isn't used.

Go to this link for your reference and other troubleshooting procedures https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections

Do not hesitate to message us if you need further assistance.

If the answer is helpful kindly click "Accept as Answer" and up vote it.

Answer 3

Hi all,

thanks for your answers, however I did not say that linked server using kerberos delegation does not work at all.

Both instance are running in a Windows Server Failover Cluster, except that they run usually in different nodes.

Also client connection does happen on same domain, since our test was to test connection from linked server through SSMS installed in both cluster nodes.

Therefore links you provided are answers to what it looks like a generic linked server that uses kerberos token to authenticate.

The situation I'm describing is the following:

Two cluster nodes C1 and C2 joined the same domain.

Let's say instances A and B are running in C1 (using WSFC not AOAG). If I use linked server set up in instance A that points to B, it works but I guess it's using a NTLM token.

When B runs on C2 (as it has requested to be by our customer), client connection now has to double hop, therefore it tries to use Kerberos token. But it does not work until a simple session is

opened by service user (the domain user created to run sql server engine and used also for agent).

After the mentioned session is successfull, suddenly all other logins (belonging to same domain as cluster, service users and client) can use kerberos delegation.

If that session has not been opened, it returns NT Anonymous\logon.

I hope to hear about you soon.