Execute AS clause does not apply to linked server queries - by Matthew B

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
0
Sign in
to vote
ID 411248 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/4/2009 2:19:52 PM
Access Restriction Public

Description

This in follow up to case # SRX081230601024 where the MS Support Engineer asked me to submit an issue through Connect. I have a linked server established between SQL 2005 and an AS400. I set this up primarily for the following benefits (in order):

1.	Use Windows Authentication & SQL’s security model for access to AS400 data.
2.	Be able to easily switch between live data (queries to the linked server) and cached data on SQL.
3.	Provide other developers within my organization with a single repository for data access (as well as a single security model).

This was my first implementation of a linked server, and almost immediately I discovered that Linked Servers are not securable, which means that the linked server is available to every server level login. You can’t restrict access to the Linked Server based on Security Context, you can only pass along credentials. I am trying to establish one credential for application level access and enforce least privileges through SQL (which is easier to maintain and is better documented). 

Feature Request: Make Linked Servers securable.

Since I couldn’t secure a Linked Server I had to work around it. Here’s what I did:

1.	Create a proxy database on SQL
2.	Map credentials between the proxy database owner and the linked server. Specify any connections without explicitly mapped credentials will not be made.
3.	Create stored procedures in the proxy database that use the ‘WITH EXECUTE AS OWNER’ clause to execute in the security context of the owner (the owner always being the proxy db owner, which would give the stored procedure authorization to the Linked Server).
4.	Use SQL’s security model to restrict access to the proxy DB’s stored procedures.

This was perfect. I had one credential back to my AS400, and I could use SQL’s security to implement least privileged access by any number of applications. Life was good.

Until I hit a bug in SQL. For some reason SQL will always compile queries against a linked server using the security context of the caller, regardless if a ‘WITH EXECUTE AS’ security context is specified. The irritating part is I didn’t discover this for some time because once a stored procedure has been compiled it can be successfully executed by any authorized user, regardless of their linked server credentials.

BUG: Compiling stored procedure with an ‘EXECUTE AS’ context switch that queries a linked server uses the Caller’s security context.
Sign in to post a comment.
Posted by Microsoft on 3/23/2009 at 11:06 PM
Dear Customer,

We are closing the issue since we have not heard back from you in response to our previous communication. Should you have any questions or would like to re-open the case to request a fix prior to the next release, feel free to contact us or one of the CSS representatives who will be happy to go over the options with you.

Thanks again for reporting this problem and for helping us improve the quality of SQL Server.

Regards,

Joachim Hammer

Program Manager
SQL Server
Posted by Microsoft on 2/18/2009 at 3:53 PM
Dear Customer,

Thank you for pointing out this issue with EXECUTE AS and distributed queries. We acknowledge that this is a limitation that makes EXECUTE AS unusable for stored procedures involving linked servers. We are working on documenting the limitation and the available workarounds in a KB article and in Books Online. We are also considering how to best address the issue in the next major release of SQL Server.

We trust that your CSS contact can help you with exploring workarounds around this problem. Please let us know if you have any questions or need further assistance.

Best regards,

Vassilis Papadimos