Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

alt text

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Connections are set to "Be made using the login's current security context". My login is able to see all catalogs on either server, though. –  Chris Wenham Jul 23 '09 at 14:15
    
It was permissions after all. I had to add "NT AUTHORITY\ANONYMOUS LOGON" as a user to the 2005 catalogs I wanted to be visible from 2000. –  Chris Wenham Jul 23 '09 at 14:31
    
Oooh, yikes. Not sure that's what you want to do as that pretty much allows anyone access to your databases. –  squillman Jul 23 '09 at 14:34
    
Well, for some reason it isn't logging-in as the current user. It only wants to go in as the anonymous logon, even though I'm a user on both machines. –  Chris Wenham Jul 23 '09 at 14:39
    
It's because SQL has to use 2-hop authentication (impersonation) to connect to the target server which requires delegation permissions. See my edit for more info! –  squillman Jul 23 '09 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.