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.
