Linked Server Problem

We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved. We get the following error when we try to query a table on the 2000 database. I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ).

OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure".

Msg 233, Level 16, State 1, Line 0

Named Pipes Provider: No process is on the other end of the pipe.

Msg 18456, Level 14, State 1, Line 0

Login failed for user 'sa1'.

Anyone have any ideas?

Thanks!!!

could be a permission problem with the linked server login

make use of the useslelf option of the addsqlserver login

to properly map users to the linked server

sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'

[ , [ @.useself = ] 'useself' ]

[ , [ @.locallogin = ] 'locallogin' ]

[ , [ @.rmtuser = ] 'rmtuser' ]

[ , [ @.rmtpassword = ] 'rmtpassword' ]

Arguments

[ @.rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @.useself = ] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.

A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @.locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @.rmtuser = ] 'rmtuser'

Is the user name used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[ @.rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

examples

A. Connecting all local logins to the linked server by using their own user credentials

The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts'

B. Connects a specific login to the linked server by using different user credentials

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts

by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'