Linked Servers Connection Issue – Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

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)

For help, click:

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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.