On SqlServer 2000 I have created a Linked Server to a SqlServer 2005 machine, with "Server type" set to "SQL Server" (first radio button), and the linked server name set to the remote machine's hostname.
But SqlServer 2000 can only see ONE of the many catalogs that are on the 2005 server. I can SELECT from the tables in that one catalog just fine, but I can't access any of the other catalogs on the same 2005 server.
What are some of the settings I could look for to figure out why this is happening, or is there a limit to the number of catalogs SqlServer 2000 can see on a linked server?
Best Answer
You will need to edit the security settings of the linked server to specify a login on the SQL 2005 instance that has permissions to all of the catalogs you wish to access through the linked server. I don't have a SQL 2000 server around anymore to give you the exact steps, but here is an MSDN article describing how to establish security for SQL 2000 linked servers.
EDIT:
See this article on SQL Server Central for configuring Kerberos authentication in order to allow linked servers to use the credentials of the currently logged in user in order to authenticate against the target server. See also the answers to my question on setting up trust delegation for SQL Server.