Linked server connection failed

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.

https://docs.microsoft.com/en-us/powershell/module/activedirectory/get-adserviceaccount?view=windowsserver2022-ps

More information:

Microsoft Made an Easy Button for SPN and Double Hop Issues

Double-Hop Linked Servers

https://help.zaptechnology.com/zap%20cubexpress/currentversion/content/topics/HowTo/UsingSPN.htm

https://social.technet.microsoft.com/wiki/contents/articles/717.service-principal-names-spn-setspn-syntax.aspx

https://kb.parallels.com/en/124644

https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

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/

https://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=soc-n-%5bTR%5d-loc-%5bServices%5d-%5bfarukc%5d