Problem when changing context (impersonation) and using a server link in SQL Server 2005

Question

I get a strange behaviour when I combine impersonation with database links in SQL Server 2005. First, I connect to a database server using simple SQL Server Authentication with Login 'John'. On this server, a server link remote_sqlserver is defined. I already have SELECT privileges for mydb in this server. When I simply query a table on a DB on this server link:

SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable  -- Works!

After that, I try impersonation with the same Login (don't ask why would one do that, I'm just experimenting ;) )

EXECUTE AS LOGIN = 'John'

SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable  -- Error: "Login failed for user: 'John'"

When I revert, it works again:

REVERT

SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable  -- Works!

Do you have any idea, why I get an error with impersonation, although the same Login can query the table without impersonation?

BTW: After "impersonation as self", if I query a local database, (of course, for which I have enough privileges) I don't get any error. It only happens when I query a remote DB via server link.

Answer 1

You should read the Books Online article "Extending Database Impersonation by Using EXECUTE AS".

When you use EXECUTE AS to access a remote server, the remote server has to be configured to trust the caller. Even though the the "parent" and "child" logins are the same (i.e. "John") since you are using EXECUTE AS the trust relationships have to be set up. The authentication "path" is different even though the login is the same.

Answer 2

If you havent already it might be worth having a quick read up on EXECUTE AS and Ownership Chains in case they can shed any light the problems your having

Answer 3

Wouldn't it be interesting if authentication would work by simple means of trusting who you say you are? One would say "I'm John, give me all the money in my account" and the bank would pay up the cash. Now, fortunately, the authentication systems used everywhere will be just a tad more demanding and when one shows up and say "I'm John", he will be challenged "Hullo John, so... what is your password?".

Exactly the same thing goes on here. You may notice that when you say EXECUTE AS Login = 'John' you did not provide a password. So the SQL Server instance may have been 'fooled' that you're "John", but nobody outside SQL will believe you (and the "fooling" inside SQL is a long story of trust and privileges in fact, what really happens is more like "I am the SYSADMIN and I SAY that thou shall believe this user is John!".

If you want to access anything outside the SQL Server system and be John, then you need to specify John's password. The usual way is by using a credential object, with CREATE CREDENTIAL.