Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
    
Ah, then I have misunderstood how it was working. So if I understand correctly, xp_cmdshell was probably limited to only allow DOMAIN\ID2 to execute it, preventing other user IDs from executing it unless that ID has been given rights to impersonate the ID2. I'm really growing to love MSFT security setups... :| –  BBlake Jun 26 '12 at 19:41
4  
It works like this: while you're under EXECUTE AS, whithin th realm of SQL Server you *are DOMAIN\ID2*. But, even under EXECUTE AS, as soon as you leave SQL Server the truth is revealed: you are again ID1. Basically SQL Server cannot tell Windows 'this is ID2, trust me' because had such API exist, it would quickly be abused as I pointed out. Windows trust only one proof to recognize ID2: present the password when challenged. –  Remus Rusanu Jun 26 '12 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.