Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server - Login Timeout Expired Expand / Collapse
Author
Message
Posted Thursday, September 10, 2009 7:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 2:01 AM
Points: 797, Visits: 1,040
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?
Post #785691
Posted Thursday, September 10, 2009 8:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 30, 2011 6:27 PM
Points: 43, Visits: 120
Refer this link to resolve your issue.

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
Post #785725
Posted Thursday, September 10, 2009 8:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:42 AM
Points: 481, Visits: 318
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
Post #785748
Posted Thursday, September 10, 2009 8:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 2:01 AM
Points: 797, Visits: 1,040
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.
Post #785751
Posted Thursday, September 10, 2009 9:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 2:01 AM
Points: 797, Visits: 1,040
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.
Post #785773
Posted Friday, April 4, 2014 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 1:11 AM
Points: 1, Visits: 22
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.
Post #1558437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse