When setting up Linked Server with in SQL Server you get following error message:
TITLE: Microsoft SQL Server Management Studio
“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)
The SQL Server you are trying to setup linked server for does not allow delegation. So in order for you to get linked server working you’ll have to first get SPN setup by server administrator in AD.
Lets say we are trying to setup Linked Server on two SQL Servers, SSA and SSB. We have a domain account on SSA called Domain\U1 that needs access to information on SSB.
- Create a new SQL Account on SSB with VERY strong password, lets call it SqlServerAccount_U2.
- Grant the SQL Account on SSB access to resources that are needed.
- On SSA under linked server security, click “ADD” to add a local->remote mapping.
- Under local login select Domain\U1.
- Level Impersonate unchecked.
- Under remote User SqlServerAccount_U2.
- Under remote password enter the VERY strong password you created.
- Select ‘Not to be made’ under for logins not defined in the list above.