Thursday, December 27, 2012 7:51 PM
I am trying to query linked server using impersonation. So here is my problem.
1.) There are two logins in my server. Login A and Login B. Login A is security login and Login B is user login. All the linked server are configured using Login A but Login B need needs to query linked server sometimes for debugging purpose.
To do that, Login A runs following query
Grant Impersonate on Login::[Login A] to [Login B]
and Login [B] runs following command to query link server
Execute as login='Login A'
Select from <LinkedServer>.DB.dbo.<table>
and getting following error<dir>
OLE DB provider "SQLNCLI" for linked server <Linked Server Name> returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server <Linked Server Name> 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 5, Level 16, State 1, Line 0</dir><dir></dir>
Named Pipes Provider: Could not open a connection to SQL Server .
I understand above error can be caused by various reasons. I have ran the above command without ExecuteAS and runs just fine.
Can anyone tell me why I am getting this error when I try to run using ExecuteAS?
- Edited by Anil Dangol Thursday, December 27, 2012 9:11 PM
Thursday, December 27, 2012 9:56 PM
"However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error."
Thursday, January 03, 2013 8:26 AMModerator
With the introduction of EXECUTE AS, crossing link servers as an impersonated user or login becomes a possibility. To make this happen, the principle (user\login) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy.
To make the databases trustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuing an ALTER DATABASE command such as this:
ALTER DATABASE mydatabase SET TRUSTWORTHY ON
TechNet Community Support
- Marked As Answer by Iric WenModerator Friday, January 04, 2013 9:16 AM