SQL Server 2000 creating a linked server to SQL Server 2008

Question

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.

Answer 1

I have use this steps and configuration options:

• SqlServerEnterpriseManager\Security\Linked Servers\New Linked Server

  • General:
    • Linked server: mylinkedservername
    • Provider name: Microsoft OLE DB Prvider for SQL Server
    • Product name:
    • Data Source: (my server name)
    • Provider string:
    • Location:
    • Catalog:
  • Security:
    • Be made using this security context:
    • Remote login: (my sql remote user. I use sa for the test)
    • With password: (my remote password)
  • Server Options:
    • DataAccess: checked
    • RPC: checked
    • RPC Out: checked
    • Use Remote Collation: checked

• Qry used in the test:

  • select * from [linkedservername].[database].dbo.[table]

Answer 2

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.