EXECUTE AS does not appear to be changing security context

Question

We have legacy code that still makes use of xp_cmdshell calls. When we migrated to SQL Server 2008, we created a stored procedure that used code after the following pattern:

EXECUTE AS 'DOMAIN\ID2'
EXEC master..xp_cmdshell @command
REVERT

When I pass in WHOAMI as the command, what it shows is not the 'DOMAIN\ID2', but rather the ID of the service account that SQL Server is running under (i.e. 'DOMAIN\ID1'). Should it not be returning 'DOMAIN\ID2' as it's supposedly running unders a different security context? If so, any idea why it would not be changing context? This process was created by another dev who is now long gone and I'm not really familiar with security and impersonation as I probably should be.

Answer 1

Wouldn't be nice to find a tool that allows you to impersonate to Windows a user without knowing the password? Every intern could impersonate the CFO and withdraw millions from the bank... The possibilities!

No, EXECUTE AS is changing the security context exclusively with regard to access SQL Server objects (tables, procedures etc). By no means will (or even can, for the matter) be able to change the real NT execution context for the process or for processes launched by SQL Server.

Update

I obviously forgot to add the actual useful piece of information: there is a way to make xp_cmdshell use a specific credential, provided you do create such a credential in SQL Server:

create credential [##xp_cmdshell_proxy_account##] 
   with identity = 'DOMAIN\ID2',
   secret = '<ID2 password>';

The key difference is that you are very explicitly giving the password here to SQL Server. Now SQL Server will use this credential 'proxy' whenever faced with calling xp_cmdshell from a SQL login context or from a EXECUTE AS context.

You can also associate a credential with a login for 'generic' access to resources outside SQL Server (eg. accessing a network share):

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.