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.