Login with EXECUTE AS failed on linked server after stored procedure was altered

Question

Dear Friends,

I have a stored procedure that accesses tables on a remote server via a linked server.

I use the EXECUTE AS 'userA' clause when creating the stored procedure.

On the linked server, I created a mapping for userA.  I also created a mapping for 'sa'.

I set the database as TRUSTWORTHY and granted the DBO AUTHENTICATE SERVER privileges.

Using the SSMS client, I do the following:

I can call the stored procedure with 'sa' successfully.

I can then call the stored procedure with userB (being impersonated by userA), successfully.

Now, using 'sa', I script the stored procedure as ALTER and execute the ALTER script.  Effectively not changing the stored procedure, but just ALTERing it.

Next, I call the stored procedure as userB and I get a login failure on the remote server for userB.

Next, I call the stored procedure as 'sa' successfully.

Next, I call the stored procedure as userB again (being impersonated by userA) and it executes successfully.

Both SQL Servers are at version 2008 R2 SP1 (x64).  10.50.2811.0.

It seems that my configuration is successful until I alter the stored procedure, after which I get a login failure, and then everything is "fixed" after I execute the stored procedure again as 'sa'.

Another thing that seems strange to me is that I get the same behavior when I use a .NET client to execute the stored procedure as both 'sa' and userB.  However, when I "fix" the problem with the .NET client, it doesn't "fix" it for the SSMS client, and vice versa.  Executing the stored procedure as 'sa' "fixes" the stored procedure only for the client that I execute from.

Please let me know if you want more information or clarification.

Thank you for any help with this.

Answer 1

just to confirm one thing: when you have altered the SP; did you included EXECUTE AS clause?

---

Kindly mark the reply as answer if they help

Answer 2

Hello Sunil,

Thank you for your reply.  Yes, when I script the stored procedure as ALTER it does include the EXECUTE AS clause.

Answer 3

First of all, beware of that making a database TRUSTWORTHY granting the database owner AUTHENTICATE SERVER, means that this database owner if he is malicious can elevate his permissions to sysadmin.

Next, it cannot work out anyway, because EXECUTE AS impersonates a user not a login. If it works sometimes may be due to that an existing connection at the remote server can be reused.

Here is something which may work (but I have not tested it):

Write a stored procedure that includes the statement EXECUTE AS LOGIN for a login that has a mapping to the remote server. Sign this procedure with a certificate that exists both in master and the database. Create login from this certificate and grant this login IMPERSONATE on the mapping login.

I have a longer article on my web site that discusses both certificate signing and EXECUTE AS in detail, although I don't cover this particular scenario:
http://www.sommarskog.se/grantperm.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hello Erland,

Thank you very much for your response.  The information that you provide is very helpful.  I'm trying an alternative based on what you have said, but haven't been successful yet.  I find your article to be beautiful, good explanations, warnings and examples.

However, I'm still not convinced of your statement above that "...it cannot work anyway...".  In the article at http://msdn.microsoft.com/en-us/library/ms188304(SQL.105).aspx, I find the following:

• "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."
• "In SQL Server, you can selectively extend the scope of the database impersonation established within a database by establishing a trust model between the two databases."
• "While this example demonstrates extending the scope of impersonation to allow access to an object in an external database, it is also possible to extend scope of impersonation to the instance of SQL Server. For example, if the procedure were to create a login, which is a server-level action that requires a server-wide permission, the AUTHENTICATE SERVER permission would have to be granted to the authenticator of the context."

Based on these statements and the explanation about TRUSTWORTHY and AUTHENTICATE SERVER, it still seems to me that my successful results are correct, and that the login failure is the incorrect occurrence.  I'm not saying that I know for sure, I'm just saying that I still see that my approach does not seem incorrect.

If the "fix" that I'm using turns out not to be acceptable, and if my approach is not correct, then I might just fall back on individual mappings on the linked server instead of impersonation, although this will introduce an additional ongoing maintenance task of maintaining these mappings, as users are added and removed from the role that has execute permission on the stored procedure.

Again, thank you very much for your response.

Answer 5

You may be correct that EXECUTE AS clause + TRUSTWORTHY works. All these combinations are quite confusing and every once in a while I have to check my article for reference!

But if that works, it should also work if you put EXECUTE AS LOGIN in the stored procedure itself, sign the procedure with the certificate and have a login that is tied to the cert and that login is granted IMPERSONATE on the user.

The reason I'm uncertain as with linked servers we stray outside the boundaries of SQL Server. But the login mapping is still local so that should work.

On the other, what is to unlikely to work is if you are logged in server A as DOMAIN\USER1 and want to access server B where only DOMAIN\USER2 has access, and there is no login mapping. If you do "EXECUTE AS LOGIN = 'DOMAIN\USER2'" you can play USER2 in the local server, but I don't expect server B to accept the login token. Again, this is not something I have not tested.

---

Erland Sommarskog, SQL Server MVP, [e-mail]