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?