Executing Stored Procedure that accesses another SQL instance

Question

I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation.

Desired Outcome

A user using SQL authentication has execute permissions to Database1 on Server1 (default instance) and that is it. The user executes a stored procedure that, as part of its process, accesses Database 2 on Server1\Instance2. I would like it to be safe and simple (both are important).

More Info

My windows credentials has access to both instances (which are on the same server). Therefore, I can execute the stored procedure under my login without difficulty. However, I don't want to give the user my level of access. I also need to use a SQL login since the user will not be on the domain.

What I would like would be to give the stored procedure my level of access just for that procedure. Since I am a sysadmin, that would give the user everything they needed for that procedure. If I got that to work, I would probably create an account just for that purpose instead of using mine, but either way it would be safe since I control what the stored proc does.

I tried putting the "WITH EXECUTE AS" statement in my stored proc but I couldn't get it to take my windows login information. When I put it in, I would get the following error upon compiling the stored proc:

Cannot execute as the user 'domain\jdoe', because it does not exist or you do not have permission.

The user is sysadmin on both servers, like I said, so I'm not sure what more it needs.

I have looked into the following:

• TRUSTED - I would rather not expose my database and this looks scary
• Linked server - I don't want to give extra permissions. I don't trust the other database to have access to my database and I don't trust my database to have access to all of the other database.
• Certificates - This seems complicated and difficult. Unless I could find a very simple way to do this and maintain it, I'm not sure it is worth the trouble.
• Ownership chaining - Again, scary. It looks like this causes more security issues when my goal is to prevent security issues.
• Mirrored user - I've even created the same (different SID obviously) user on the other server instance and gave it the same password. No go.

I feel like I am missing something obvious but I'm not sure what it is. Since I've been banging my head against the wall all day on this, I'm probably too close to see it. I would very much appreciate it if someone here could give me a hand or point me in the right direction. I will say that I have read a lot of the MSDN articles (boy do I hate them - they never seem to tell me what I want to know). What I would really like is a simple, easy to follow tutorial that walks me through how to do this. Short of that, even a general indication of the direction I need to go would be helpful.

Answer 1

Try using EXECUTE AS LOGIN = 'DOMAIN\username' instead and see if that works.

Answer 2

Take a look at using EXCUTE AS + Trustworthy . You can set it up where it can be called within the stored procedure as long as user b has been given access and the two databases trust each other.

This guys blog should answer or provide everything you need. http://www.sommarskog.se/grantperm.html#EXECAScrossdb

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

Answer 3

After reading extensively on the topic and doing a number of experiments, I believe I have come to a conclusion on this matter. The EXECUTE AS statement is not designed to work cross-instance without major security implications. What I was hoping for was a way to tell my procedure what Windows identity I wanted to run under, since a Windows identity can have access to multiple resources on multiple servers. However, even after playing around with a bunch of different settings, it became apparent that I would have to weaken other security measures in order to allow a stored procedure to impersonate me.

There doesn't seem to be a lot of information out there about cross-instance or cross-server procedures. I would imagine the reason for this is because of the security and performance implications of doing so. However, I believe there are cases where it is important and it seems like the solutions to doing so are complicated and very scenario-specific. I came across a good article that helped me at least understand some of my options. It wasn't focused on cross-instance access but it did give me the clues I was looking for. I would encourage you to check it out:

http://www.sommarskog.se/grantperm.html

I would still be interested in other solutions to this problem, but my solution right now is two-fold. First, if I absolutely need to access two databases via one stored procedure, I have to use a Windows login. I avoid this whenever possible, however, since it does cause performance issues (multi-server locking, network complications, inability to optimize the query, etc.) Second, I bring the data from each database through separate, database-specific calls. That means I bring the data back to the client before merging it. It isn't as performant or as clean as I would like, but it seems to be the safest solution.