Is xp_cmdshell Security threat???

Question

Is xp_cmdshell Security threat???

rambilla4

SSCrazy Eights

Points: 8715
More actions
June 22, 2009 at 12:17 pm

#135383

Hi,
We are using xp-cmdshell for deleting old backups. But I heard that xp_cmdshell is a big security threat for SQL Server. Is it true?

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

rambilla4 (6/22/2009)
Hi,
We are using xp-cmdshell for deleting old backups. But I heard that xp_cmdshell is a big security threat for SQL Server. Is it true?

That depends. Do you consider this code a threat?

exec master..xp_cmdshell 'FORMAT C:'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

the_SQL Ten Centuries Points: 1014 More actions · Answer 2

The short answer is yes, xp_cmdshell is a dangerous object to enable. There are several ways around using xp_cmdshell, and it would be worth your while to research your alternatives.

Karl Lambert
SQL Server Database Administration
Business Intelligence Development

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

It is really only dangerous in the case where appropriate and strict adherence to minimum required permissions and complex password safeguards are lacking or non-existent. Unfortunately this is true in too many situations and has resulted in giving this rather useful procedure a bad name. Most people take the brute force (easier) approach and disable the feature.

There are many ways to hack a database, this is but one of the more interesting ones. Having said that, proper adherence to SQL Server security on objects and logins along with application coding designed to be injection-proof will make this particular procedure no more of a danger than DROP TABLE. I'll get flamed for saying this but it is a fact.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

sturner (6/22/2009)
It is really only dangerous in the case where appropriate and strict adherence to minimum required permissions and complex password safeguards are lacking or non-existent. Unfortunately this is true in too many situations and has resulted in giving this rather useful procedure a bad name. Most people take the brute force (easier) approach and disable the feature.
There are many ways to hack a database, this is but one of the more interesting ones. Having said that, proper adherence to SQL Server security on objects and logins along with application coding designed to be injection-proof will make this particular procedure no more of a danger than DROP TABLE. I'll get flamed for saying this but it is a fact.

I know this is an old thread but I wanted to add... I absolutely agree. It's not the tool that's bad. It's the way that people implement 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: 1004356 More actions · Answer 5

WayneS (6/22/2009)
rambilla4 (6/22/2009)
Hi,
We are using xp-cmdshell for deleting old backups. But I heard that xp_cmdshell is a big security threat for SQL Server. Is it true?
That depends. Do you consider this code a threat?
exec master..xp_cmdshell 'FORMAT C:'

I know this is a wicked old thread but I have to ask... who can use that command? The answer is "Only people with SA privs" or people that the DBAs where stupid enough to grant a direct execution proxy to.

That being said and assuming that no one and no thing but the DBAs have the privs to execute xp_CmdShell, why do you think xp_CmdShell provides a security threat?

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Jeff Moden (4/6/2013)
WayneS (6/22/2009)
rambilla4 (6/22/2009)
Hi,
We are using xp-cmdshell for deleting old backups. But I heard that xp_cmdshell is a big security threat for SQL Server. Is it true?
That depends. Do you consider this code a threat?
exec master..xp_cmdshell 'FORMAT C:'
I know this is a wicked old thread but I have to ask... who can use that command? The answer is "Only people with SA privs" or people that the DBAs where stupid enough to grant a direct execution proxy to.
That being said and assuming that no one and no thing but the DBAs have the privs to execute xp_CmdShell, why do you think xp_CmdShell provides a security threat?

Geez Jeff, getting bored and reading threads that have been dead for years?;-)

I'm in the boat that it isn't so much of a threat if proper controls are in place. And for places where controls are lacking - audit.

There are good uses for cmdshell. They are being replaced with powershell these days - but not everybody is up to snuff on PoSH.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Nope. Not bored. Doing research. My take on it, so far, is that disabling xp_CmdShell to supposedly enhance security is like holding up a bath towel to protect you from a nuclear blast. 😛 If no one can use it other than SAs, then what is "everyone" so bloody afraid of? If someone that isn't supposed to, gets in as SA, it's not gonna matter if it's disabled or not. Some claim that it's an extra "layer" of security than an attacker would have to go through and I say they don't even have to use xp_CmdShell to raise hell at the OS level if they get in with SA privs.

To wit, if they think that turning off and not using xp_CmdShell is some form of security, I'm really concerned about what they think security is.

On the PowerShell thing, I use xp_CmdShell to call PowerShell. 😉 Between the two, a DBA can do some awesome stuff in a very secure manner.

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