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.
Best Answer
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.