SQL server impersonation in linked server

Question

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

Use Master;

Grant Impersonate on Login::[Login A] to [Login B]

and Login [B] runs following command to query link server

Execute as login='Login A'
Go

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 [5].

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?

Answer 1

http://msdn.microsoft.com/en-us/library/ms188304%28v=sql.90%29.aspx

"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."

Answer 2

Hi Anil,

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 Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Iric Wen
TechNet Community Support