Best Approach To Enable and Allow Use xp_cmdshell Non sysAdmin Users

Question

Best Approach To Enable and Allow Use xp_cmdshell Non sysAdmin Users

shoffman-569213

Say Hey Kid

Points: 697
More actions
July 7, 2010 at 10:46 am

#387291

I have some developers that are using xp_cmdshell and to get it to work I had to grant administer bulk operations for a network group. What does this allow the network groups to do, can they administer or change something? I also had to grant execute on xp_cmdshell which when locking down SQL Server 2005 sounds like a security hole (comments??) I also had to use sp_xp_cmdshell_proxy_account and configure an account which I set to SQL Server service account but as of now this is a permanent proxy account, should the account be temporary and then remove the proxy account after use in the stored procedure? The example below is from Tibor Karaszi.
i.e.
EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'
EXECUTE AS login = 'JohnDoe'
--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
--Cleanup
EXEC sp_xp_cmdshell_proxy_account null
Thanks for the help!!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 1

shoffman-569213 (7/7/2010)
I have some developers that are using xp_cmdshell and to get it to work I had to grant administer bulk operations for a network group. What does this allow the network groups to do, can they administer or change something? I also had to grant execute on xp_cmdshell which when locking down SQL Server 2005 sounds like a security hole (comments??) I also had to use sp_xp_cmdshell_proxy_account and configure an account which I set to SQL Server service account but as of now this is a permanent proxy account, should the account be temporary and then remove the proxy account after use in the stored procedure? The example below is from Tibor Karaszi.
i.e.
EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'
EXECUTE AS login = 'JohnDoe'
--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
--Cleanup
EXEC sp_xp_cmdshell_proxy_account null
Thanks for the help!!!

What is it that they're actually trying to do using xp_CmdShell? And, yep... it matters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shoffman-569213 Say Hey Kid Points: 697 More actions · Answer 2

The developers are using xp_cmdshell and the OS dir command to verify that the file path and file exists before loading the contents and doing further processing. Using a file is not the best way but the IS manager says for now to use the existing file rather than bring the contents into a table so we have to interact with the file.

sturner SSC-Insane Points: 22411 More actions · Answer 3

grant them execute on xp_fileexist

execute xp_fileexist @pathToCheck, @status

if @status = 0

The probability of survival is inversely proportional to the angle of arrival.

sturner SSC-Insane Points: 22411 More actions · Answer 4

grant them execute on xp_fileexist

execute xp_fileexist @pathToCheck, @status

if @status = 0

print 'Not There'

Sorry for the double post, my keyboard glitches all the time.

The probability of survival is inversely proportional to the angle of arrival.

shoffman-569213 Say Hey Kid Points: 697 More actions · Answer 5

Thanks I will look into the xp_fileexist

For educational purposes can you give me a rough outline of how to safely give access to xp_cmdshell and bulk administer rights as well as how to handle the proxy account?

Thanks!

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 6

shoffman-569213 (7/8/2010)
The developers are using xp_cmdshell and the OS dir command to verify that the file path and file exists before loading the contents and doing further processing. Using a file is not the best way but the IS manager says for now to use the existing file rather than bring the contents into a table so we have to interact with the file.

It's easy, then. Try this and see the multiple possibilitites without the need for xp_cmdshell... and, yes... it will also accept an UNC that the server login can see. Hopefully your SQL Server login has the necessary privs...

EXEC Master.dbo.xp_DirTree 'C:\',1,1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shoffman-569213 Say Hey Kid Points: 697 More actions · Answer 7

Thanks for the responses and your help, I greatly appreciate it!!

EdVassie SSC Guru Points: 60472 More actions · Answer 8

For educational purposes can you give me a rough outline of how to safely give access to xp_cmdshell and bulk administer rights as well as how to handle the proxy account?

The FineBuild Reference document has details on best practices when creating a xp_cmdshell proxy account. This covers the security considerations, and how the xp_cmdshell proxy relates to SQL Agent job proxies.

The ultimate best practice is where ever possible use lower-risk alternatives to xp_cmdshell, such as the the examples given by Jeff and sturner.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara