Friday, February 10, 2012

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

SQL Server 2005/2008: Linked Server issue

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

After creating a linked server, I tried to run some queries against it. But I kept getting the error mentioned above. I was using an ID that was domain administrator so I was unable to think of any reason why my ID would be considered an anonymous login.

Upon further research I found several recommendations online that suggested that I should be using either a LOCAL SYSTEM Account or an ID that is not explicitly created on the remote server. But that was not the case, as my SQL Server agent and the SQL Server service account were using a domain account. So I created a login for my ID on the remote server. In spite of that I was still receiving the error.

So I thought of creating a mapping between a local login and a remote login

But I had mapped a local windows account to a remote windows account and I received the following error.

Msg 18456, Level 14, State 6, Server <server name>, Line 1
Login failed for user '<user name>'

State 6 mean I am trying to use a windows account to login to SQL Server using SQL Server authentication.
So as a final attempt I mapped a local windows account to a remote SQL Server login. Before doing that I created that SQL Server user on the remote server and granted it appropriate permissions.

That seemed to do the trick. Going forward for every local windows account that needed to access the linked server, I mapped it to the same SQL Server login on the remote server. 

No comments:

Post a Comment