sp_addlinkedserver on sql server 2005 giving problem

Question

I am trying to create a link server of a remote database(both the servers are SQL serve2005). I am able to connect that remote server from my SQL Server management studio. I used the following syntax to create it.

EXEC sp_addlinkedserver
@server        = N'LINKSQL2005',
@srvproduct = N'',
@provider     = N'SQLNCLI',
@provstr       = N'SERVER=IP Address of remote server ;User ID=XXXXXX;Password=*********'

I have provided the IP addressntax. and user name and password in the above syntax. The link server is getting created. But when I try to execute a query on it I get the error below.

Query Used.

select * from LINKSQL2005.<DBName>.dbo.<TableName>



OLE DB provider "SQLNCLI" for linked server "LINKSQL2005" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'sa'.
OLE DB provider "SQLNCLI" for linked server "LINKSQL2005" returned message "Invalid connection string attribute".

Pls help me, where am I making mistake.

Answer 1

I am able to find the solution. The following 2 lines will solve the problem.

sp_addlinkedserver '<Link Server Alias Name>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL 

sp_addlinkedsrvlogin '<Link Server Alias Name>', 'false', NULL, 'username', 'password' 

Then execute the Query for testing.

select * from <Link Server Alias Name>.[<DB Name>].dbo.<TableName>