1

I am setting up a linked server using kerberos authentication, but I am running into a problem that I have not seen before. I have set up linked servers using kerberos previously without problems.

On the same domain, we have four servers - we can all them SQL1, SQL2, SQL3 and SQL4. They are run by three separate AD service users SVC-SQL1 (used for both SQL1 and SQL2 servers), SVC-SQL3 and SVC-SQL4 - all service users can read and set SPNs.

All service users and the servers have Kerberos delegation activated for all services and SETSPN verifies that all have SPNs set as do the errorlogs when starting up the SQL services on the machines.

However, an AD user with sysadmin privilege on all servers using kerberos can:

  • SQL1 can connect and query SQL2 but not SQL3 or SQL4
  • SQL2 can connect and query SQL1 but not SQL3 or SQL4
  • SQL3 can connect and query SQL1 and SQL2 but not SQL4
  • SQL4 can connect and query SQL1 and SQL2 but not SQL3

In all the cases where a server cannot be connected to, the error message is: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

From a single SSMS session it is possible for the sysadmin user to connect to any of the servers by their name and execute queries locally using windows authentication.

The servers are all virtualised using VM-Ware and they are distributed this way: SQL1, SQL2 and SQL4 are on VM1 and SQL3 is on VM2. They are on the same internal network and Windows Firewall has been disabled on all servers.

Each servers return successful powershell Test-NetConnection -Computername [name] -Port 1433 for all other servers individually.

It would seem that SQL3 and SQL4 somehow refuses the connections from any remote servers when using linked server to connect. Is there any configuration parameter or option that I am overlooking when setting this up?

Improve this question
2
  • 1
    Can you clarify if the tests are all linked server connections where the client is remote? Can you clarify whether the client session to the first hop is using NTLM or Kerberos in sys.dm_exec_connections.auth_scheme?
    – David Browne - Microsoft
    Commented Aug 12, 2021 at 13:25
  • The client is remote (a SSMS application running on a citrix desktop). The connection is made using Kerberos in all cases.
    – Rasmus Remmer Bielidt
    Commented Aug 12, 2021 at 19:18

1 Answer 1

Reset to default
2

This is likely a AD delegation issue with the service accounts.

All three services needs to be configured to allow delegation.

This can be configured in AD using the "Active Directory Users and Computers" utility.

The best practice is to use "constrained" delegation and explicitly list the SPN's a service can delegate permissions to.

  • SVC-SQL1 needs to delegate to the SPN's for SQL3 and SQL4
  • SVC-SQL3 needs to delegate to the SPN's for SQL1, SQL2 and SQL4 (likely already configured for SQL1 and SQL2)
  • SVC-SQL4 needs to delete to the SPN's for SQL1, SQL2 and SQL3 (likely already configured for SQL1 and SQL2)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.