Execute xp_cmdshell command as specific user

Question

I would like to run xp_cmdshell (TSQL procedure) in order to mount a network drive and then access remotes mdb files.

I am administrator on the MS SQL server and I have allowed xp_cmdshell execution accordingly.

However, there is still a problem:

• When I call xp_cmdshell, the user executing the command is the SQL SysAdmin, i.e. the account who run SQL Server process.

• I wish xp_cmdshell executes as the account with which I'm connected to SQL server, i.e Administrator

Both of theses account are in administrator group, SQLAdmin group, and are granted to CONTROL SERVER. Both users belong to the same domain. All of this is run on the same machine.

Because of this conflict, I cannot use a network drive because it is mounted for SysAdmin and not for Administrator
I tried to use sp_ xp_ cmdshell_ proxy_ account to specify the account with which I want to run xp_cmdshell, but SysAdmin is still the used account.

Therefore, this code :
select user_name(), suser_name;
exec xp_cmdshell 'echo %username%';

displays :
Administrator Administrator
SysAdmin

Does anybody knows how to impersonate well the xp_cmdshell command ? Is there something to (re)configure?

Thanks for your help.

Answer 1

Because you're connecting to SQL as a login in the sysadmin group, xp_cmdshell runs as the service account.

If you connect as a low-privilege login, then it will use the xp_cmdshell_proxy_account instead. So try doing EXECUTE AS LOGIN='lowprivaccount' first, to see if that helps.

Of course, what you're actually asking is not the expected use. Expected use is that the high-privilege accounts can allow xp_cmdshell to use the Service Account, whereas everyone else has to put up with the lower privilege proxy account.

Answer 2

I actually have had to use this method in the past for similar things on network shares, try this...

-- map your drive and make it persistent.

xp_cmdshell"net use t: \\<server>\<share> <password> /user:<username> /persistent:yes"

-- t-sql code making use of the t drive

-- delete the drive mapping xp_cmdshell"net use t: /delete"

you can actually set up a job that executes when sql service starts and make it map this drive so you will always have access to the share as long as sql is running. All you would need to do is setup a sproc that maps the drive and have it do the initial mapping of the drive and make use of sp_procoption (http://msdn.microsoft.com/en-us/library/ms181720.aspx)