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

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?”
——————————
ADDITIONAL INFORMATION:

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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3310&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————

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.

Ref: http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx

Thanks.

About these ads
Categories: Linked Servers
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 241 other followers

%d bloggers like this: