SQL Server 2000 creating a linked server to SQL Server 2008

hyperlinksql-server-2000sql-server-2008

In SQL Server 2000, I'm trying to create a linked server to SQL Server 2008. Everything I've tried raises errors of one flavor or another.

In SQL Server 2000 Enterprise Manager, I:

  1. Right-click Linked Servers under the server's Security node, then choose New Linked Server… and name the link "SQL2008R2"
  2. I choose the "Other data source" radio button because I don't think SQL Server 2000 can natively connect to 2008.
  3. I choose "SQL Server Native Client 10.0" in the Provider name field, type "SQL2008R2" in the Product name field, enter "VIRTUALXP-62910\SQLEXPRESS" (Servername and named instance name) in the Data source field, leave Provider string blank, and type the database name "RWUtils" in the Catalog field.
  4. On the security tab, I type "Link" as the local login and as the Remote user, and enter the password. I choose the "Not be made" radio button for logins not listed above. I previously set up a "Link" user on both servers, with the same password, with access to the right databases.
  5. I click OK to create the link. The SQL2008R2 link is created.

When I expand the link I just created and click on Tables, I get the error,

Error 7416: Access to the remote
server is denied because no
login-mapping exists.

Can anyone tell me what I'm doing wrong or what the correct sequence of steps or T-SQL is to create this link successfully?

Thanks much.

Best Answer

I just did this for the first time (after seeing your question) and it worked - going from SQL 2000 Standard 32-bit (default instance) to a named instance on SQL 2008 R2 64-bit.

Using similar steps to what you have above:

  1. fine
  2. choose "SQL Server" as the Server Type, enter servername\instance
  3. not applicable
  4. I chose "Be made using this security context", and entered in my new SQL Standard Account that exists on the target server only (2008 R2).
  5. I tested with a query and it worked. Be sure new SQL Standard user has access to the database and table. Query should look like: select * from [linkedservername\instance].[database].[dbo].[table]

Other notes: I took the defaults on the Server Options tab, which are "Data Access", "RPC", and "RPC Out" all checked.

The other direction: I previously had it working the other direction from SQL 2008 R2 linked server to SQL 2000. That required this: http://blog.raffaeu.com/archive/2008/06/19/sql-2005-and-linked-server-cannot-obtain-the-schema-rowset.aspx.