The Danger of xp_cmdshell

Question

Post reply

The Danger of xp_cmdshell

Steve Jones - SSC Editor

SSC Guru

Points: 734416
More actions
September 15, 2016 at 9:08 pm

#321479

Comments posted to this topic are about the item The Danger of xp_cmdshell

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

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

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

Heh... I think I'm being prodded to publish. 😀

--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: 3874 More actions · Answer 2

I think the risk is reasonable, as long you are using an xp_cmdshell_proxy_account with minimal privileges.

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.

God is real, unless declared integer.

David.Poole SSC Guru Points: 75894 More actions · Answer 3

Publish Jeff!

I'd ask what people wanted to use xp_cmdshell for. There are loads of things you can do within SQL Server, for me a request to make calls out to the OS is a potential indicator that someone has a hammer and everything is a nail.

They may be completely justified in what they want to do and how they want to do it, in which case fine. I'd always check though.

LinkedIn Profile

David.Poole SSC Guru Points: 75894 More actions · Answer 4

t.franz (9/16/2016)
Ior an 'internal' T-SQL bcp command), so that we do not need xp_cmdshell.

BULK INSERT? Or are you lamenting the absence of BULK EXPORT?

I quite like MySql LOAD XML INFILE & LOAD DATA INFILE, but not the way those commands coerce the data into their target without throwing an error. Come to think of it I wasn't too impressed when an INSERT INTO...VALUES statement with the fields in the wrong order resulted in a string containing filenames being coerced into an integer field. Had a few head scratching moments trying to find out where the large integer values were coming from.

LinkedIn Profile

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 5

BULK INSERT? Or are you lamenting the absence of BULK EXPORT?

I'm missing BULK EXPORT.

Another point: even if you can do some OS-like stuff with SQL Server, the account that runs the SQL Server service has to have the privileges to do that. So it could more secure to do something with a proxy account that e.g. has no permissions on the SQL data / backup directory.

God is real, unless declared integer.

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 6

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

As soon as I read the title I thought of your blood pressure 😉

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 7

t.franz (9/16/2016)
BULK INSERT? Or are you lamenting the absence of BULK EXPORT?
I'm missing BULK EXPORT.
Another point: even if you can do some OS-like stuff with SQL Server, the account that runs the SQL Server service has to have the privileges to do that. So it could more secure to do something with a proxy account that e.g. has no permissions on the SQL data / backup directory.

The missing "BULK EXPORT" is the number 1 reason outside of DBA tasks that I have seen xp_cmdshell is needed. I know ssis can be used but that can be overkill

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

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.

Along these lines:

DECLARE @ReturnCode INT -- xp_cmdshell returns 0 if the file is found

EXEC @ReturnCode = master.dbo.xp_cmdshell "dir c:\STATS.dfhdfh", no_output

IF @ReturnCode = 0

PRINT 'File exists!'

ELSE

PRINT 'File not found'

B's-Data SSChasing Mays Points: 607 More actions · Answer 9

We use xp_cmdshell in a large number of stored procedures that we call nightly. Every time I enable this setting on a new server build, I get a bit concerned, but have yet to see any adverse affects and I've been seeing it used for 17 years. I believe, I could be wrong, but with the enhanced security in windows and SQL server as well, it is not as easy to hack a system using this command.

For now I will continue to enable it where it is needed.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

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

"Prodded"????

My first thought on reading this was "oh, dear, Steve just shot a pork chop at Jeff Moden"! 😀

Rich

Gary Gwyn SSC-Addicted Points: 443 More actions · Answer 11

As @Indianrock does, I turn it on only when needed. I recall having to write a custom database archival script. Within the script a database was backuped up, dropped, and copied to an archival path. I used xp_cmdshell to move the files. I scheduled the script to run in SQL Agent, but had extra job steps to enable and disable xp_cmdshell.

Blog: http://sqlexchange.wordpress.com

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

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.

Eric M Russell SSC Guru Points: 125518 More actions · Answer 13

I find it convenient to use xp_cmdshell to do things like execute NET or DOS commands for informational purposes, like in environments where the host server doesn't allow RDP connections.

Even when enabled, it's not usable by non sysadmin logins by default, and you have to explicitly jump through hoops to grant access. If your non-DBA users do have sysadmin privillage, they'll simply enable it themselves and then use it. If you tick them off by disabling xp_cmdshell, they'll simply drop your login. The bottom line is: don't grant non-DBA users membership in sysadmin role. Just follow that basic advice and a lot of these issues raised in forum discussions here simply won't be an issue, at least not for your organization.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Paulo A. Nascimento SSCommitted Points: 1667 More actions · Answer 14

Of course not... people are dangerous not computers or commands.