The Danger of xp_cmdshell

Question

The Danger of xp_cmdshell

Viewing 15 posts - 31 through 45 (of 62 total)

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

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

Can we expect xp_fileExist to continue functioning? Isn't it undocumented?

FrancoisFlageole Grasshopper Points: 12 More actions · Answer 2

I just happened to work today on a script that uses that XP_CMDSHELL. I need to produce a file from a DB, and to export it to a directory. Several ways can be used to accomplish that, one of them by using the "in"famous command.

The script checks if the command is allowed. If not (as expected), I allow its usage within the script, export the data, then disallow its usage (if it was the case before).

So I use it, but only within my script, and only for the purpose of what I need, no more and no less.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

FrancoisFLageole (9/16/2016)
I just happened to work today on a script that uses that XP_CMDSHELL. I need to produce a file from a DB, and to export it to a directory. Several ways can be used to accomplish that, one of them by using the "in"famous command.
The script checks if the command is allowed. If not (as expected), I allow its usage within the script, export the data, [font="Arial Black"]then disallow its usage [/font](if it was the case before).
So I use it, but only within my script, and only for the purpose of what I need, no more and no less.

Just to be semantically correct, you're not actually disallowing its use. You're only putting it into the "disabled" mode any anyone that has sysadmin or "control server" can re-enable it. Usage has not been "disallowed".

That's certainly a part of my point, though. Why even bother disabling it? Who's going to use it when it's enable? The answer is, only the same people that can enable it unless someone did do the worst practice of granting a user that doesn't have sysadmin privs to run it directly.

--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: 1003857 More actions · Answer 4

Indianrock (9/16/2016)
Can we expect xp_fileExist to continue functioning? Isn't it undocumented?

Heh... Consider that you can't even rely on fully documented features to continue functioning. 😉

--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: 1003857 More actions · Answer 5

Indianrock (9/16/2016)
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.

That's what I meant. What kind of functionality have you been able to achieve with PowerShell that you've not been able to achieve with xp_CmdShell?

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

FrancoisFlageole Grasshopper Points: 12 More actions · Answer 6

Jeff Moden (9/16/2016)
FrancoisFLageole (9/16/2016)
I just happened to work today on a script that uses that XP_CMDSHELL. I need to produce a file from a DB, and to export it to a directory. Several ways can be used to accomplish that, one of them by using the "in"famous command.
The script checks if the command is allowed. If not (as expected), I allow its usage within the script, export the data, [font="Arial Black"]then disallow its usage [/font](if it was the case before).
So I use it, but only within my script, and only for the purpose of what I need, no more and no less.
Just to be semantically correct, you're not actually disallowing its use. You're only putting it into the "disabled" mode any anyone that has sysadmin or "control server" can re-enable it. Usage has not been "disallowed".
That's certainly a part of my point, though. Why even bother disabling it? Who's going to use it when it's enable? The answer is, only the same people that can enable it unless someone did do the worst practice of granting a user that doesn't have sysadmin privs to run it directly.

You are right, and I stand corrected (French is my first language, I use English rarely! ) So yes, anyone with the right privilege can turn it on and off at will.

To add a precision to my case, several "DB Admins" can use the servers I work on, and we do not always know what the others are doing in regards to DB-specific or Server-specific settings :unsure: (Yes, I know, we should do a better job in this area, but..)

mtassin SSC-Insane Points: 23099 More actions · Answer 7

Jeff Moden (9/16/2016)
That's certainly a part of my point, though. Why even bother disabling it? Who's going to use it when it's enable? The answer is, only the same people that can enable it unless someone did do the worst practice of granting a user that doesn't have sysadmin privs to run it directly.

I don't prefer to use it... but sometimes you essentially have little choice. As a company we're going whole hog into Salesforce and other cloud systems.

At first we had planned to use SSIS to access SF in order to transfer information back and forth either to our data warehouses or from our phone system to create support cases and the like.

Then we hit on DBAMP. It's an interesting system that lets you build a linked server to Salesforce and run queries against it. The problem is it uses xp_cmdshell to run an executable. We love it... our ability to adjust with the business at the speed of the business has increased dramatically.

Normally I don't do things that need it, so xp_cmdshell is off.. but nowadays with the needs of things like DBAMP, it's on... the only people who can run it are sysadmins, and that list is thankfully small.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

roger.plowman SSChampion Points: 10265 More actions · Answer 8

This one is "convenience over security" to me, not to mention feature creep.

xp_cmdshell really is a kind of a bad idea from an architectural standpoint, isn't it?

I mean the point of using SQL Server (or competitors) is to keep the data out of the bad guys hands and safe from bumbling good guys, right? Basically data theft and accidental destruction prevention?

xp_cmdshell opens a big gaping hole in your defenses so you can do non-SQL Server things from SQL Server. If SQL Server is a hammer then everything starts to look like a nail...

SSIS (or equivalent) is better at bulk data transfer, and data staging imports are best performed via custom-built applications, not SQL Server. Handling all the different import formats is definitely not SQL Server's strong suit after all.

Personally I like having minimal surface area. Less to defend, smaller target, and all that.

Oh, and those that miss BULK EXPORT? (shudder). Four words "data exfiltration made easy".

But that's just the cynical paranoia talking, right? Right?

Slyfin SSC Enthusiast Points: 181 More actions · Answer 9

Personally, I don't prefer to use it but I have. The really messed up thing was we had a Microsoft Consultant to do a health check of our environment. The analysis script flagged us for xp_cmdshell being enabled. That didn't sound right to me and after further research I found that their script turned it on.

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

Slyfin (9/16/2016)
...we had a Microsoft Consultant to do a health check of our environment. The analysis script flagged us for xp_cmdshell being enabled. That didn't sound right to me and after further research I found that their script turned it on.

Gotta justify the fee 😛

Gaz

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

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

mtassin (9/16/2016)
Jeff Moden (9/16/2016)
That's certainly a part of my point, though. Why even bother disabling it? Who's going to use it when it's enable? The answer is, only the same people that can enable it unless someone did do the worst practice of granting a user that doesn't have sysadmin privs to run it directly.
I don't prefer to use it... but sometimes you essentially have little choice. As a company we're going whole hog into Salesforce and other cloud systems.
At first we had planned to use SSIS to access SF in order to transfer information back and forth either to our data warehouses or from our phone system to create support cases and the like.
Then we hit on DBAMP. It's an interesting system that lets you build a linked server to Salesforce and run queries against it. The problem is it uses xp_cmdshell to run an executable. We love it... our ability to adjust with the business at the speed of the business has increased dramatically.
Normally I don't do things that need it, so xp_cmdshell is off.. but nowadays with the needs of things like DBAMP, it's on... [font="Arial Black"]the only people who can run it are sysadmins[/font], and that list is thankfully small.

Careful now... does DBAMP have an application login that uses xp_CmdShell? You may be in for a nasty surprise.

--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: 1003857 More actions · Answer 12

roger.plowman (9/16/2016)
This one is "convenience over security" to me, not to mention feature creep.
xp_cmdshell really is a kind of a bad idea from an architectural standpoint, isn't it?

Yes... almost as bad as SSIS and letting folks run PowerShell. 😛

I mean the point of using SQL Server (or competitors) is to keep the data out of the bad guys hands and safe from bumbling good guys, right? Basically data theft and accidental destruction prevention?

Couldn't agree more. That' why it's so important to keep the bad guys from getting in with "sysadmin" or "control server" privs.

xp_cmdshell opens a big gaping hole in your defenses so you can do non-SQL Server things from SQL Server. If SQL Server is a hammer then everything starts to look like a nail...

When all you're trying to do is hit nails, what's wrong with using a hammer. And, no. xp_CmdShell doesn't open a big gaping hole in security. Having big gaping holes in security does. 😉 Only sysadmin and "control server" roles can use xp_CmdShell directly (unless you've made the huge mistake I previously spoke of). Those are the same people that can enable it. People even with DBO privs can't use it directly. Proper use in stored procedures makes it there is no need for anyone other than DBAs to have the ability to run it directly and the stored procedures that users would run should have the same safety built in against SQL INJECTION as any other stored procedure.

You can disable xp_CmdShell and maybe setup some policy that will prevent it from being enabled but anyone that breaks in with either of the two privs I mentioned won't be stopped by any of that. Of course, they also don't need xp_CmdShell to ruin your day.

SSIS (or equivalent) is better at bulk data transfer, and data staging imports are best performed via custom-built applications, not SQL Server. Handling all the different import formats is definitely not SQL Server's strong suit after all.
Personally I like having minimal surface area. Less to defend, smaller target, and all that.

While I agree that SSIS can handle more types of imports, it's not necessary in any of the work I've had to do. XML, CSV, TSV, Fixed Field, and spreadsheets are what I normally have to work with.

To say that SSIS is "better" at bulk data transfer is a pretty broad stroke of the pen and I've not found it to be true in any of the work I've had to do. Part of my job in a previous job was to actually tear down SSIS imports in favor of using BULK INSERT in T-SQL because it was much easier to promote a simple stored procedure from dev to stage to prod than it was packages (and that's change a little recently), it allowed us to decommission and reuse the hardware the SSIS servers were on, and it inherently reduced the surface area because we no longer had SSIS to worry about.

But that's just the cynical paranoia talking, right? Right?

Concerning the proper use of xp_CmdShell, correct. But it's not your fault. It's what you've been led to believe on the subject.

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

You may be able to do the same things with xp_cmdshell that you can do with powershell, but I think it's safe to say it would be considerably more difficult. Aren't we comparing DOS batch language with a full-fledged programming language complete with GUI?

Ask most developers which one they'd rather use ( or even whether they'd prefer C# over Tsql )

I generally only use xp_cmdshell for "no-brainer" things like a directory listing or checking the existence of a file.

Peter Schott SSCrazy Eights Points: 9674 More actions · Answer 14

OK, Jeff. Write your article(s). I'd love to see the "securing xp_cmdshell" article and the "how to use xp_cmdshell in a proc securely" article at the minimum. 🙂

I'd love to switch our backups over to MinionWare's backups, but need to eliminate any/all concerns about the use of xp_cmdshell before we can do that.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 15

Indianrock (9/16/2016)
...but I think it's safe to say it would be considerably more difficult.

No... It's not safe to say that. As with all else having to do with computers, "It Depends".

Shifting gears back to my original question to you, what have you done in Powershell that you didn't know how to do such a thing using xp_CmdShell?

Aren't we comparing DOS batch language with a full-fledged programming language complete with GUI?

Who want's to use a "full-fledged programming language" when you don't need it? They say the same thing about SQL. Personally, I'm VERY happy that I don't have to write loops anymore just to access data. 😉 And have you ever used WMIC? It's absolutely awesome and you usually don't need to write loops there either.

Ask most developers which one they'd rather use ( or even whether they'd prefer C# over Tsql )

I already know the answer to that... especially the developers that don't actually know anything about T-SQL. They're also not the ones that I'd trust even with the likes of SSIS to do imports with. It would be like someone asking me to write code for a GUI. 😉 Besides... when it comes to performance and security, it's not a popularity contest.

I generally only use xp_cmdshell for "no-brainer" things like a directory listing or checking the existence of a file

If PowerShell is so easy and your good at it, then why use xp_CmdShell for anything? If you do better with PowerShell, then use it. Shoot. There are even things I use PowerShell for (ImportCSV and ExportCSV are awesome for cleaning up trash in files that were generated from spreadsheets)... but I call it with xp_CmdShell so I can schedule it all, do it from stored procedures, and get quick returns through T-SQL.

Shifting gears a bit, this whole thing isn't so much about personal preferences. It's about people being concerned about security. Folks continue to say that xp_CmdShell is a security risk and it is only if you don't understand how to use the tool correctly. As they said over at MidNight DBA, DELETE, DROP TABLE, and TRUNCATE (to name a few) are horribly dangerous commands unless you control privs or even if you do. Should we not use those?

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