SQL Server Linked Server error and Query Problem

Question

I have two servers running SQL Server 2000 and SQL Server 2005.
There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database.

I have a linked server pointing from SQL Server 2005 database A to SQL Server 2000 database B. Here the scenario is I can able to access database SQL Server 2000 database B from SQL Server 2005.

Two issues I face I am randomly getting the error:

OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

How to resolve this error?

Also, when the user want to access the database located in the same server what must be done? I mean accessing the database SQL Server 2005 database A and SQL Server 2005 database A i.e ignoring the linked server.

• Mike Walsh: On the SQL2000 server - what is the error message in the error log? There will likely be a corresponding 18456 message. How are you authenticating? My guess here is it has to do with kerberos and needing to have delegation - though I don't understand why it sometimes works.
• SenthilPrabhu: @MikeWalsh: SQL Authentication
• Mike Walsh: How have you configured the security for the linked server? Please provide a screenshot. with the server and login names redacted.
• Ionic: Maybe you have an authentication problem or your network can't reach the other server. The second thing can be checked quite quickly if you logon on server A and try to Telnet to Server B on the right port.

Answer 1

Need some more information for the Linked Server error - asked a question in a comment for more info there and will clean this up based on your thoughts there. Though it appears as though you are experiencing issues with kerberos delegation on the face of it. (More info here)

For the second question - it sounds like you are just asking how can that user connect directly to the SQL2005 instance without using a linked server. If I've understood correctly - you just grant their login access to the SQL Server 2005 server (either a SQL authenticated login or a domain login) and grant them the database and server access they need. Then they can use SQL Server Management Studio or whatever tool you use to connect and connect directly.