Execute xp_cmdshell command as specific user

问题:

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.

回答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.

回答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)

回答3:

Maybe you could try PsExec? Download the file at this URL and copy it in a folder member of the %Path% environment variable.

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

exec xp_cmdshell 'psexec -u Administrator -p password net use ...'

回答4:

You could try "net use" with a username and password inside xp_cmdshell. This establishes the credentials for the connection to the UNC.

However, I'm not sure how long this would persist. If it persists indefinitely (eg until server restart), you could have a start-up stored procedure that does "net use" and ensures it's available for use later.

A subsequent xp_cmdshell (to access the MDB files) would not require the authentication because the credentials are already established within the OS.

回答5:

I found this page helped fill in the gaps in the process of actually adding the domain account and linking it.

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

回答6:

After restart server must execute command plase solution save command...

Use Master GO

EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH 
OVERRIDE GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE GO

exec xp_cmdshell 'net use \ip\xxx pass /user:xxx /persistent:no'

Use Master GO

EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH 
OVERRIDE GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE

回答7:

You must create a stored procedure to which you will place your xp_cmdshell script in it.

A stored procedure runs using the administrator account, therefore your xp_cmdshell will successfully run when you execute the stored procedure

create procedure RunShellIndirectly

as

declare @tawandachinaka as varchar(50)

set @tawandachinaka='DIR "c:\scrap measurement\"*.csv /B' 

EXEC xp_cmdshell @tawandachinaka