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.