patternMinor
EXECUTE AS does not appear to be changing security context
Viewed 0 times
securitydoescontextchangingnotappearexecute
Problem
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:
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.
EXECUTE AS 'DOMAIN\ID2'
EXEC master..xp_cmdshell @command
REVERTWhen 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.
Solution
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,
Update
I obviously forgot to add the actual useful piece of information: there is a way to make
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
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.
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 = '';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.
Code Snippets
create credential [##xp_cmdshell_proxy_account##]
with identity = 'DOMAIN\ID2',
secret = '<ID2 password>';Context
StackExchange Database Administrators Q#19974, answer score: 8
Revisions (0)
No revisions yet.