0

In SQL Server 2017 I have a Linked Server defined to use a fixed remote SQL Login for all local users.

EXEC master.dbo.sp_addlinkedserver 
@server = 'RemoteServer', @srvproduct='SQL Server';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',@useself='False',
@locallogin=NULL,@rmtuser='remoteUser',@rmtpassword='pass123';

This Linked Server can be used successfully by any local SQL Login, but not by a Windows login.

USE [master]
CREATE LOGIN [MyDomain\myUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

When this Windows user is logged in, attempts to query the Linked Server timeout.

SELECT * FROM [RemoteServer].[RemoteDatabase].[dbo].[RemoteTable]

The exact error message is this:

OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "Login timeout expired".

OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

I know that network connectivity is not a problem because the Linked Server is working correctly for local SQL Logins.

What am I doing wrong?

Actually I was trying to run the above select from inside a stored procedure with the fixed execution context of a user linked to a SQL Login.

USE [master];
CREATE LOGIN [localUser] WITH PASSWORD='pass456', DEFAULT_DATABASE=[master];

USE [LocalDatabase];
CREATE USER [localUser] FOR LOGIN [localUser];
GO

CREATE PROCEDURE dbo.MyStoredProcedure
   WITH EXECUTE AS 'localUser'
   AS
   SELECT * FROM [RemoteServer].[RemoteDatabase].[dbo].[RemoteTable]

But executing the stored procedure is also timing out, which makes me think the impersonation doesn't affect the Linked Server query at all.

What can I change to get the above to work? Unfortunately, I'm not able to make changes to the remote server. Much grateful for any suggestions.

4
  • Is there any error message on the remote server when it fails for the windows login? – Ronaldo Jun 20 '20 at 15:19
  • The remote server is not accessible except through the local Linked Server. – dlh Jun 20 '20 at 20:40
  • "Server is not found or not accessible' does not match the title of the question. Please clean up the question and test network connectivity from the local server to the remote server with test-netconnection in Powershell for the target server and port. – David Browne - Microsoft Jun 20 '20 at 23:11
  • I did confirm that the Linked Server was online. It is accessible when the local account is a SQL Login rather than a Windows Login. The credentials used in the Linked Server definition do allow me to select. – dlh Jun 21 '20 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.