Does anyone have a good reason to run xp_CmdShell?

Question

Does anyone have a good reason to run xp_CmdShell?

Jeff Moden

SSC Guru

Points: 1004302
More actions
June 10, 2011 at 9:55 pm

#239049

During a recent thread, someone asked a very good question that I'd like to get folk's opinions and/or actual experience on.
As a precursor to the question, understand that there are, in fact, 2 different methods to make it where a "PUBLIC" user can execute a stored procedure which contains a call to xp_CmdShell without that user being able to execute xp_CmdShell directly themselves.
So, the question is, knowing the precursory information above and given tools such as SQL Agent, SSIS, SQLCLR, and PowerShell, is there any good reason to use xp_CmdShell in a stored procedure? If so, please tell me what that reason is.
Thanks for the help, folks.
--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)

Viewing 15 posts - 1 through 15 (of 107 total)

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

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

BWAA-HAAA!!!! 48 reads and 2-1/2 days later and no one has a compelling reason to use xp_CmdShell? Considering the number of posts asking how to use it, I'm really surprised! 😀

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

Honestly, I recommend against using it these days. CLR procedures can do anything it can do, more securely. So, in an SQL 2000 environment, I can see using it, but not in anything later.

Of course, turning on CLR has its own security issues, but they can be mitigated more effectively.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 3

lol...i use it myself whenever i feel like it...but can i justify using it at all; it was the fastest to grab tool in the toolbox...nah, can't justify myself at all, and I'll openly admit it.

problem for me is, the tools to replace what i use it for are more complex to use than the original

so building compiling, testing and deploying a CLR to do some of those things don't fit into my time management constraints as well as enabling xp_cmdshell for myself, doing whatever i needed to do, and then turning it off again.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Don Halloran SSCertifiable Points: 5937 More actions · Answer 4

I just wrote a set of procedures to assist with backup/copy/restore. They're built to work with Ola Hallengren's backup and maintenance procedures, taking care of copying the backups and doing restores to the DR site (or any other instance, really).

I use xp_cmdshell to execute a robocopy.

But this may not qualify, since these procedures are in a schema with permission restricted to a specific AD group (database admins and the service account).

The reason why I use xp_cmdshell instead of CLR is that file handling in .NET is not pretty. Robocopy does the job far more easily, plus from within the procedure I can easily query configuration tables which influence the operation. I *could* just start a process from within a CLR procedure, but why go to that level of obfuscation?

SDB15 SSC Eights! Points: 949 More actions · Answer 5

allmhuran (6/13/2011)
I just wrote a set of procedures to assist with backup/copy/restore. They're built to work with Ola Hallengren's backup and maintenance procedures, taking care of copying the backups and doing restores to the DR site (or any other instance, really).
I use xp_cmdshell to execute a robocopy.

+1

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 6

I tend to use SQLCMD (that is if PowerShell is not available to me :-D). We disable access and use of xp_cmdshell.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 7

GSquared (6/13/2011)
Of course, turning on CLR has its own security issues, but they can be mitigated more effectively.

That's interesting. Thanks, Gus. You mention more effective mitigation of security issues when using CLR's... what does that mitigation involve?

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

Shawn Melton (6/13/2011)
I tend to use SQLCMD (that is if PowerShell is not available to me :-D). We disable access and use of xp_cmdshell.

Understood. Thanks for the feedback.

What do you use to execute the SQLCMD or PowerShell script and how do you handle the login security for the SQL Server they work with?

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 9

Lowell (6/13/2011)
lol...i use it myself whenever i feel like it...but can i justify using it at all; it was the fastest to grab tool in the toolbox...nah, can't justify myself at all, and I'll openly admit it.
problem for me is, the tools to replace what i use it for are more complex to use than the original
so building compiling, testing and deploying a CLR to do some of those things don't fit into my time management constraints as well as enabling xp_cmdshell for myself, doing whatever i needed to do, and then turning it off again.

That's actually some excellent information and fits right in to why I asked the question. Thanks, Lowell.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Because I can!

Reasonning stopped there.

10+ users have sa password and the rest are dbos.

I have good backups (restored + checkdb) with 24 / 7 PIT. Stopped the worry process right around there :w00t:.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 11

allmhuran (6/13/2011)
The reason why I use xp_cmdshell instead of CLR is that file handling in .NET is not pretty. Robocopy does the job far more easily, plus from within the procedure I can easily query configuration tables which influence the operation. I *could* just start a process from within a CLR procedure, but why go to that level of obfuscation?

That was one of my original thoughts, as well. Why redevelop the wheel when there's so much out there that does file handling via a CMD session so very well. Good Stuff. Thank you for the feedback.

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 12

Ninja's_RGR'us (6/13/2011)
Because I can!

BWAA-HAAAA!!!! I'm right there with you except my line of reasoning would be "Because I LIKE it!" 😀

Thanks for the feedback, Remi.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I like it too. Been around since dos 4.? so everything comes pretty naturally to me there... no point in learning clr and reinventing the wheel.

I mainly use it to get file lists, delete old backups and regenerate restore commands for PIT... maybe free space as well.

Since many power users are also SA, I couldn't care less about the security risk. Both the vm and db are backed up so if someone really screws up with can recover and say "told you so" and maybe get this back under control ! 😉

Ah the challenges of only being a dev !!

george sibbald SSC Guru Points: 104210 More actions · Answer 14

Ninja's_RGR'us (6/13/2011)
I like it too. Been around since dos 4.? so everything comes pretty naturally to me there... no point in learning clr and reinventing the wheel.
I mainly use it to get file lists, delete old backups and regenerate restore commands for PIT... maybe free space as well.
Since many power users are also SA, I couldn't care less about the security risk. Both the vm and db are backed up so if someone really screws up with can recover and say "told you so" and maybe get this back under control ! 😉
Ah the challenges of only being a dev !!

there are sub cmdhell (sic) xps that do these sort of tasks. Not as easy to use I grant you but they exist (xp_delete_file, xp_fileexist etc)

---------------------------------------------------------------------