6

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

Community
  • 1
Chris Wenham
  • 189
  • 1
  • 1
  • 8

3 Answers3

5

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.

Community
  • 1
squillman
  • 37,883
  • 12
  • 92
  • 146
  • 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
  • 1
    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
  • I checked the article and used SETSPN to add MSSQLSvc service principal names for hostname, hostname.domain, and IP address for the user that SQLServer runs under. Not a sausage. No Kerberos authentication at all. – Chris Wenham Jul 24 '09 at 15:16
  • Also verified that SQLServer was running on port 1433 and added the SPNs both with and without the port specified. – Chris Wenham Jul 24 '09 at 15:19
4

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.
FuriousD
  • 141
  • 2
0

I just did it!

The Remote login ID that is used to linked all the server to one another, has to have read/write access to the database.

As soon as you give read and write access to the database, the database will show in the list of catalogs under the linked server.

You go to each database > Security > users and find the Remote Login. If it is not there, go to the server level > Security > Users > User Mapping and check the database to be linked. Now, make sure that the Remote login has read/write permissions, by going to the database > Security > Users > Owned Schema check the db_datareader and/or db_datawriter.

Good luck!

Jawid

Jawid
  • 1