Product:
Microsoft SQL server 2016
Issue:
Error when testing linked servers in SQL server.
Solution:
Ensure the port is open first, by start PowerShell on the from server and use Test-NetworkConnection. Enter below to check port 1433;
tnc servername -port 1433
If firewall is open, and you get a error saying “anonymous login” then the kerberos delegation does not work. You need to check that your account (or the account that run the SQL agent service) is allowed to be delegated, check with this power-shell command;
Get-ADUser -Filter {SamAccountName -eq "YourAccountSamName"} -Properties msDS-AllowedToDelegateTo | Select-Object -ExpandProperty msDS-AllowedToDelegateTo
You must check that the SQL server machine, have in AD allow to delegate, you do this with this PowerShell command (for both servers);
Get-ADComputer ComputerName -Properties * | Format-List -Property *delegat*,msDS-AllowedToActOnBehalfOfOtherIdentity
TrustedForDelegation must be True.
Ask the Active Directory team to correct above.
Get-ADServiceAccount -Filter {SamAccountName -eq "YourServiceAccuntNameHere"} -Properties * | Format-List -Property *delegat*
Above need to be used, if the account is a Service Account in AD.
More information:
https://help.zaptechnology.com/zap%20cubexpress/currentversion/content/topics/HowTo/UsingSPN.htm
https://kb.parallels.com/en/124644
https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/
https://www.c-sharpcorner.com/UploadFile/suthish_nair/linked-servers-in-sql-server-2008/
https://en.hackndo.com/constrained-unconstrained-delegation/