September 10, 2009 at 7:55 am
Hi,
I'm having trouble using a linked server when impersonating a login. If I run this from Server1:
EXECUTE AS LOGIN = 'Domain\User1'
SELECT TOP 1 * FROM [Server2].master.sys.databases
I get this:
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
If I don't run EXECUTE AS, the linked server works fine. The linked server security is set to "be made using the login's current security context". Remote connections are enabled on both servers. User1 is a sysadmin on both servers.
Any ideas as to why this is happening?
September 10, 2009 at 8:25 am
Refer this link to resolve your issue.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
September 10, 2009 at 8:52 am
I was getting error message something similar to what you see. I noticed that using EXECUTE AS is dishonored when you enable option "be using logins' current security contexxt".
you may also getting error message because of this, though I am not 100% sure.
Did you try logging to sql server using domain account that you are trying to use as EXECUTE. Also did you try to login to remote sql using the same domain user.
Swarndeep
http://talksql.blogspot.com
September 10, 2009 at 8:53 am
Thanks, I did read that article earlier. The linked server doesn't have any connectivity issues when used directly, it's only through using the EXECUTE AS clause that problems begin. The issue is actually with a SQL Server agent job that runs if the job owner is a sysadmin but returns a login timeout error if the user is removed from the sysadmin server role. The job uses a linked server.
September 10, 2009 at 9:11 am
Yes, if I log in as the user on both servers it works fine and the linked server works. It's only using EXECUTE AS, which is what I think is happening when the user is not a sysadmin and the SQL Agent job is executed. If the user is a sysadmin, the job is just executed under the security context of the SQL Server service account. If the user is not a sysadmin, I get the same error as when using EXECUTE AS.
April 4, 2014 at 5:02 am
Did you manage to resolve this, I'm having a similar issue only with Service Broker and internal activation strored procedure trying to execute a procedure using a linked server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply