The Danger of xp_cmdshell

Question

The Danger of xp_cmdshell

Viewing 15 posts - 16 through 30 (of 62 total)

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

mike 62740 SSC Rookie Points: 28 More actions · Answer 1

I've found that there are some app developers that misuse it when it gets enabled. It has lead to some silly surprises at times from developers who do not know how to work with a database.

For jobs, I've started using a lot more PowerShell for things I used to use xp_cmdshell for... just a trade off.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

mike 62740 (9/16/2016)
I've found that there are some app developers that misuse it when it gets enabled. It has lead to some silly surprises at times from developers who do not know how to work with a database.
For jobs, I've started using a lot more PowerShell for things I used to use xp_cmdshell for... just a trade off.

What's to keep the app developers from misusing Power Shell? And what, explicitly, do you mean by "some app developers that misuse it". How are they misusing it?

--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: 1004340 More actions · Answer 3

Indianrock (9/16/2016)
So far I only recall trying to use powershell from a sql agent job once, but this might be a better route than xp_cmdshell.

How so?

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

Thomas Franz Hall of Fame Points: 3894 More actions · Answer 4

Indianrock (9/16/2016)
I use xp_cmdshell in sql jobs, but turn it on before the action and turn it off after. The one that comes to mind is using DIR to check for the existence of a file that indicates billing files are ready to be imported to a database with SSIS.

You should try

DECLARE @DirectoryInfo TABLE (FileExists bit,

FileIsADirectory bit,

ParentDirectoryExists bit)

INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)

EXECUTE [master].dbo.xp_fileexist 'c:\temp\x.txt'

God is real, unless declared integer.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

In case anyone missed it, here's a good article on the (non) dangers of xp_cmdshell:

Tech Corner: xp_cmdshell isn’t evil[/url]

by Jennifer McGowan

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Jeff Moden (9/15/2016)
Heh... I think I'm being prodded to publish. 😀

😉

David.Poole SSC Guru Points: 76000 More actions · Answer 7

Probably a bit off topic but commands like xp_dirtree have been listed as unsupported for over a decade but have been available in all that time.

Why don't Microsoft support them? It seems very odd to keep an unsupported command across many versions of a product without it being supported. It smacks of a "don't push the big red shiny highly prominent button right there on the wall. Have we mentioned that it is big and shiny....and red"?

LinkedIn Profile

mike 62740 SSC Rookie Points: 28 More actions · Answer 8

Not so simple to use PowerShell from a stored procedure if xp_cmdshell is disabled... and as I said... a trade off.

Gary Varga SSC Guru Points: 82166 More actions · Answer 9

As a dev, I have yet found a situation where I needed xp_cmdshell. Just lucky, I guess.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Jason Markantes SSC Rookie Points: 39 More actions · Answer 10

We're using xp_cmdshell for ETL's that run via java command line for a data warehouse. It is vendor supported so wasn't our decision, but it works well. We've set up proxy accounts to OS accounts with just the right privileges so it's fairly locked down, but it took some doing (especially as I was a junior dba just starting out when given the project).

I wouldn't say it's dangerous, but there are thinner margins for error.

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

mike 62740 (9/16/2016)
Not so simple to use PowerShell from a stored procedure if xp_cmdshell is disabled... and as I said... a trade off.

You can easily create a job that uses/abuses it. You still haven't said, though... how are the developers on your end abusing xp_CmdShell. Not challenging here... would just like to know and I might be able to help prevent such abuse. And, hopefully, this isn't in prod, is it?

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

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 12

t.franz (9/16/2016)
I think the risk is reasonable, as long you are using an xp_cmdshell_proxy_account with minimal privileges.

I agree the risk can be mitigated.

t.franz (9/16/2016)
On the other side it would nice, if SQL Server would provide us with more OS native operations (as file copy or an 'internal' T-SQL bcp command), so that we do not need xp_cmdshell.

YES!!!! YES!!!! YES!!! We have BULK INSERT, how about BULK EXPORT. How about being able to parameter-ize BULK INSERT. There are a number of ways that basic I/O could and should be facilitated through stored procedures.

--Paul Hunter

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Jason Markantes (9/16/2016)
We're using xp_cmdshell for ETL's that run via java command line for a data warehouse. It is vendor supported so wasn't our decision, but it works well. We've set up proxy accounts to OS accounts with just the right privileges so it's fairly locked down, but it took some doing (especially as I was a junior dba just starting out when given the project).
I wouldn't say it's dangerous, but there are thinner margins for error.

If you gave an app or anyone other than trusted DBAs the privs to run it directly via proxy, then you don't have it locked down at all. In such cases, it's running as the server god and the OS accounts can do ANYTHING and EVERYTHING that a sysadmin would be able to do.

You must NOT give any non-DBA account proxy privs to execute xp_CmdShell directly. It MUST be done through a strictly controlled stored procedure.

--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: 1004340 More actions · Answer 14

SQLNightOwl (9/16/2016)
[I agree the risk can be mitigated.

That's what I'm talking about. People think there's a risk in using xp_CmdShell and there isn't. The only risks there are are in the privs that you give people and giving anyone or anything that's not a DBA privs to run xp_CmdShell directly is the mistake. Not xp_CmdShell itself.

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

Indianrock SSC-Insane Points: 20333 More actions · Answer 15

How so? If you meant why use powershell over xp_cmdshell ( you probably didn't mean that ), the answer would be because it has so much more functionality.

If you mean why don't I use powershell more, I have no excuse.