Can't see all catalogs on a MSSQL Linked Server

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?

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.

You are receiving this problem because you have Kerberos half-way configured. You are experiencing a double-hop problem. I've learned a few things about setting up Linked Servers in the past few days. This document on 'How to Implement Kerberos Constrained Delegation with SQL Server 2008' helped me the most.

Here are some key points concerning using integrated security with linked servers (ie - 'Be made using the login's current security context')

• Your Windows Account must have access to both ServerA and ServerB.
• The servers must use TCP/IP or Named Pipes
• Both ServerA and ServerB must have their own SPN registered.
  • You may see a login failed for user ANONYMOUS error if not.
  • When using short names it MUST resolve to the FQDN with the active directory domain name. If you type in a short name and it resolves to any other domain name but your AD domain name it seems to break. As an alternative use a CNAME in the secondary domain which points to your AD domain name.
  • To check SPN: setspn -l DOMAIN\SQL_Engine_Svc_Account
  • To set SPN: I'm not 100% all of these entries are needed but these are what I added to cover all of the different ways a client might connect to the instance. Warning! These SPNs are Case Sensitive!. You must reset the instance after setting the SPNs. You can let the engine automatically register these but in my case, using dns aliases, it would never register properly.
    • Basic Syntax: setspn -A MSSQLSvc/SQLHOSTNAME[FQDN][:Port][:INSTANCE]
    • Default Instance:
      • setspn -A MSSQLSvc/HOSTNAME DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/HOSTNAME:1433 DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/HOSTNAME.DOMAIN.ORG DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/HOSTNAME.DOMAIN.ORG:1433 DOMAIN\SQL_Engine_Svc_Account
    • Named Instance:
      • setspn -A MSSQLSvc/HOSTNAME:INSTANCENAME DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/HOSTNAME.AD.ORG:INSTANCENAME DOMAIN\SQL_Engine_Svc_Account
    • Named Instance with DNS Alias: (We use dns aliases that are different from the actual hostname.)
      • setspn -A MSSQLSvc/ALIAS DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/ALIAS:1433 DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/ALIAS:INSTANCENAME DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/ALIAS.DOMAIN.ORG:1433 DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/ALIAS.DOMAIN.ORG:1433 DOMAIN\SQL_Engine_Svc_Account
      • setspn -A MSSQLSvc/ALIAS.DOMAIN.ORG:INSTANCENAME DOMAIN\SQL_Engine_Svc_Account
• The sql engine account must not have the setting "Account is sensitive and cannot be delegated."
• You must configure delegation when configuring a double hop linked server connection:
  • In active directory users & computers find the service account for SQLServerA's engine service (Assuming you're using an account - otherwise find the computer account.) Under the Delegation tab select 'Trust this user for delegation to specified services only' & 'Use Kerberos only' Click the Add button. Find the service account for SQLServerB's engine service. Select the SPNs you previously configured that pertain to the linked server you are trying to create.