EXECUTE AS and Linked Servers

Well today was one of those days where there just does not seem to be enough time to take care of everything on your plate. Of course you want to finish what you have, after all it is just sitting there, and it would be rude to not finish your meal, your host might be upset. Besides, it is always a good rule to take what you want to eat, but eat all that you take. And another good rule is that only order a “SuperSize” if you want your clothes to also come in that same size. But I digress…

So, one of the things that came up today was an issue with the use of EXECUTE AS and a linked server. The summary of what we had in place is as follows.

Take a windows login, ‘domain\user’, and use the EXECUTE AS clause:

EXECUTE AS LOGIN = ‘domain\user’

Then call a stored procedure:

EXECdbo.proc ‘parm’
REVERT

And get back this message:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

Look famliar? It certainly does to me. It reminds my team of the days fighting that evil dog Kerberos, and the famous ‘double-hop’ issue. But since we have taken steps to make sure we never see that problem again, we were fairly certain something else was wrong. Still, the message was familiar enough for us to immediately think: What linked server is being used here?

Sure enough, what we found was the inside the stored procedure we were doing the following:
WITH EXECUTE AS CALLER

Which is not really the bad part, but then we were doing a select against a view, and this view is defined to use a linked server, and the link is set to use ‘current security context’. The end result is the error message, when we would be expecting a result set. Does anyone see the issue yet? Well, SQL Server MVP Aaron Alton did, and was able to explain it in terms that even I could understand.

Aaron wrote:

Because the AD account that you’re “executing as” isn’t the one that is actually logging in and running the proc, there is no authentication token to pass over to the second server.  I’m not a security geek at this point, so an example might be better….

  – Let’s say you create two logins with their respective passwords…Gotham\bwayne and Gotham\batman
  – Gotham\batman is granted access to objects on ServerB
  – Gotham\bwayne is not, but has sufficient permissions to impersonate Gotham\batman on ServerA
  – You create a linked server to ServerB from ServerA, and ask it to pass through credentials
  – Gotham\bwayne logs in to ServerA, and impersonating Gotham\batman, tries to access an object on ServerB.

How does ServerA let ServerB know that a real, authenticated request from Gotham\batman is being passed?  In Windows Authentication, SQL Server doesn’t check the domain username and password of the principal who is trying to log in – it validates that the user has legitimately logged into the domain by contacting the domain controller (I believe the principal supplies a token to the server, which is validated by the DC).  No token will exist for Gotham\batman on Gotham\bwayne’s machine, so the authentication couldn’t possibly succeed.

Then again, I could be wrong.  I’m sure somebody will be able to verify whether or not I’m on the right track.

Now, Aaron’s explanation makes complete sense. However, it also makes sense to me that the current design we had built should work as well. Logically the idea is to create a secure account, limit access to that account directly (i.e. do not let people log in with that account or store a password in a config file somewhere), and let that account do the secure work we need it to do. Of course it is clearly not working, so I spent time poking around to see if there might be a way to get this to work. I couldn’t.

At this point I have presented a handful of options. One of them is to define a SQL login for the linked server. Other options are to not use the secure account, to remove the EXECUTE AS, and to assign permissions to the users directly. We could also move the database onto the target server and remove the need for the link, but that is not feasible if we have other systems that want to connect to that data.

Oh, I guess we could start thinking about replication, right? I mean, why store one piece of data in one location when you can store it in multiple locations, all the time? No thanks. That is more overhead than we would care to have. I would always look to be efficient with whatever we build, and the idea of dragging and dropping data all over the place just to use the EXECUTE AS functionality seems silly.

But with that off my plate for now, it frees me up to hit the Waffle House for breakfast tomorrow.