Came across this problem today when trying to call a trigger on a remote database.

I had added a linked server instance using the following command:

EXEC master.dbo.sp_addlinkedserver @server = N’<remoteserverinstancename>’, @srvproduct=N’SQL Server’

This seemed ok – i could connect to the remote master database ok:

SELECT COUNT(*) FROM [<remoteserverinstancename>].[master].[sys].[tables]

But trying to run a query against any other database failed with this error:

Named Pipes Provider: Could not open a connection to SQL Server [53]

The way i finally solved this (none of the swathe of MSDN articles related to this helped me), was to:

1) Create a local SQL user account (Windows accounts don’t seem to work) on the server to connect to, called something like: ‘<servername>_linked_server_user’ with a specified password.

2) Grant this user permissions on the database i want to connect to.

3) Update the Linked server connection on the server which runs the query through the GUI; update the security properties to make all connections use this security context as shown:

Quick test:

Run the query:

‘SELECT top 10 * FROM [<remoteserverinstancename>].[<databaseToConnectTo>].[dbo].[<tableToConnectTo>]’

and see if it works.