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?