Creating a connect to the Linked Server within SQL Server, however it fails with following error.
“The linked server has been created but failed a connection test. Do you want to keep the linked server?”
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
Often people work around this error by taking shortcut. They will use login-mapping, impersonation, or hard-code the login information.
Those methods work, however, they leave a gap in your environment. You do not know who is accessing resources on the target servers.
The recommended using “Be made using the login’s current security context” for authentication for the SQL Server linked servers. This confirms the person accessing resources has access to destination.
However, if your source server (i.e. the server you are initiating request from) is not allowed to delegate. The access request will fail with above error.
To correct this following is required:
- Source server must have SPN configured (SourceSQL1)
- Enable delegation for the service account in active directory. The service account is the SQL Server engine account on SourceSQL1.
- Confirm target SQL Server accepts Kerberos connections (review SELECT * FROM sys.dm_exec_connections).
- Restart SourceSQL1, SQL engine service.
SQL Canada (SQLCAN) 