SQL Server - Linked servers, querying one way is fine but the other?

I have two SQL Servers which have been linked using sp_addlinkedserver 'ServerB\Instance' from ServerA and sp_addlinkedserver 'ServerA\Instance' from ServerB.

If I execute the following query from ServerA then everything is okay:

SELECT *
FROM [ServerB\Instance].Database.dbo.Table

If I execute the following query from ServerB an error occurs:

SELECT *
FROM [ServerA\Instance].Database.dbo.Table

Error:

Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The service accounts that SQL Server runs under on ServerA and ServerB have been given elevated permissions on 开发者_开发知识库both servers in an attempt to solve the issue but no success.

I have done research but want to avoid a convoluted process when communication one way is okay.

I have solved this by following the process:

1. Deleted both linked servers.
2. Executed sp_addlinkedserver for ServerA from ServerA RDP (SSMS) and for ServerB from ServerB RDP (SSMS).

Previously I had executed sp_addlinkedserver for both servers from the one server RDP (SSMS) session only. Executing from each server has solved the problem. If someone can add comments as to why this is then I will be very grateful.

In SQL Management Studio, view the properties for the linked server from Server B to Server A. There's a security "tab" that you can view from there. That will tell you the security context the connection from B to A is using. The error you are getting normally occurs when you don't have a valid security context set for the linked server.

The reason that it can work from A to B and not B to A is that you need to set up the linked server correctly on each side. Just doing it for one is not going to work to go both ways.

You have to map your local user to a remote user on the other server. Do can do this by calling sp_addremotelogin() as explained here: http://msdn.microsoft.com/en-us/library/ms186338.aspx

To work around this problem, use one of the following methods:

• Map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager.

• If you are running the distributed query on an instance of SQL Server that is running on a Microsoft Windows 2000-based computer, configure SQL Server to listen for client requests by using the Named Pipes Server network library, instead of using the TCP/IP Server network library or the Multiprotocol Server network library. To configure the Server network libraries for SQL Server, use the Server Network Utility.

Take a look at:

http://support.microsoft.com/kb/238477