Linked Server problem when querying

Question

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following: 

EXEC sp_addlinkedserver @server = 'Achilles\Mixed', @srvproduct = ' ', @provider = 'SQLNCLI', @datasrc = 'Archilles\Mixed', @catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" 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 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are  Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

 

 

Answer 1

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.

 

Thanks

Answer 3

Hi,

I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.

Answer 4

I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue. 

I could not get it to pass Windows authenticated logins to the remote server at all.

In the Security Page of Linked Servers Properties dialogue.

For a Login not defined in the above list Connections will:

 

Be made using this security context:

Remote Login:  Loginname

With Password: LoginPassword

 

 

 

Answer 5

Thanks

 

created a new SQL account using the new server name and it works a treat