sp_linkedservers did not help in linking server

[Manikrag]

Hi Team,

I am trying to run a query on my local system and trying to access a table from a remote server..here is the query:

select * from SERVER.DATABASE .DBO.TABLE

This query is working fine but when I am trying to access the same from db server I am getting error as given below:

TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user 'USER'. OLE DB provider "SQLNCLI" for linked server "SERVER" returned message "Communicat ion link failure".


Please help!!

[ck9663]

Did linked the server yet?

Good Luck!!!

~~ CK

[Manikrag]

it linked after mapping a local sql user to a server sql user..but as per our dbAdmin it's not a secure way and it will have a ongoing dedicated connection both the database..which should not be the case.

Can anyone confirm that link server build a dedicated connection with the linked server?

[Manikrag]

just poasting for ppl looking ard the same solution - what really worked for me was -

EXEC sp_addlinkedsrv login @rmtsrvname = 'RemoteServerNa me'
, @useself = 'false'
, @locallogin = 'LocalSqlUSER'
, @rmtuser = 'SeverSqlUser'
, @rmtpassword = 'SeverSqlPasswo rd'


Thanks,
Manik

[sashi]

Which edition of SQL Server are you working with?

[sashi]

Can anyone confirm that link server build a dedicated connection with the linked server?

Kindly refer to below attached link, hope it helps.

Benchmark for Microsoft SQL Server 2005