The Danger of xp_cmdshell

Question

The Danger of xp_cmdshell

Viewing 15 posts - 46 through 60 (of 62 total)

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

van.yancey Grasshopper Points: 15 More actions · Answer 1

I've only used it on Azure SQL Datawarehouse, but I believe the Polybase feature in SQL Server 2016 solves the problem of BULK EXPORT.

Thomas Franz Hall of Fame Points: 3897 More actions · Answer 2

van.yancey (9/16/2016)
I've only used it on Azure SQL Datawarehouse, but I believe the Polybase feature in SQL Server 2016 solves the problem of BULK EXPORT.

But it would be a little bit overwhelmed, if you use Polybase just to create a small 1-2KB HTML file on a network share (on your web server) that contains the next few scheduled downtimes (as we do)

God is real, unless declared integer.

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

I use both xp_cmdshell and powershell. xp_cmdshell is usually for easier things that I pretty much already know how to do. I don't write powershell scripts from scratch, yet. I find one that serves my purpose and modify it -- get WMI info, get server CPU/core info, script out all sql agent jobs, list sysadmins on all servers provided in a text file, get sql instance info, get server powerplan info, etc.

So yes, whether one is easier depends on how much you've done with the tool. I know quite a bit can be done with DOS batch language and NET commands.

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

FrancoisFLageole (9/16/2016)
I use English rarely!

Heh... a lot of us in the U.S.A. have the same problem. 😀

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

Stefan LG SSCommitted Points: 1936 More actions · Answer 5

I must admit I am a big fan of xp_cmdshell!

I have been using it for many years now, without any problems (security or otherwise). I suppose there are better ways of doing certain things, but in terms of simplicity nothing beats calling the odd ‘DOS’ command.

The fact that it is disabled by default is just as annoying as the ‘Prevent saving changes that require table re-creation’ option! 🙂

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Stefan LG (9/18/2016)
I must admit I am a big fan of xp_cmdshell!
I have been using it for many years now, without any problems (security or otherwise). I suppose there are better ways of doing certain things, but in terms of simplicity nothing beats calling the odd ‘DOS’ command.
The fact that it is disabled by default is just as annoying as the ‘Prevent saving changes that require table re-creation’ option! 🙂

Spot on to both!

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

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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

Actually Microsoft fixed that more than a decade ago - if you ensure that the proxy account has no dangerous capabilities then non-SA people can use XP_CMDSHELL harmlessly (that wasn't true when I first encountered XP_CMDSHELL, but it is now); and of course if you don't create a proxy account for XP_CMDSHELL no-one but SQL Server SysAdmins can use it, and I certainly don't want every app that needs cmdshell access from SQL to have to run with SA privilege in SQL Server - that would be just plain crazy - so I'm going to create an XP_CMDSHELL proxy account that provides minimal permissions, unless I'm sure that no apps need to use XP_CMDSHELL. Unless, of course, no apps need it.

In my view, if you give someone you wouldn't trust to have a minimally privileged windows account the ability to construct and run SQL on your server you have a very insecure SQL instance. If you trust someone to run SQL, you might as well trust them to run windows commands.

I might be nice if things were organized so that anyone who logs into SQL Server with a Windows login gets his cmd_shell strings run under that Windows login; and to associate windows logins for all the SQL Server SQL logins to be used for xp_cmdshell. If you do have people you trust to run SQL Server but not DOS & windows commands you could associate those people's SQL logins with disabled windows accounts. All that is conceptually a small change, but it would probably be quite hard for Microsoft to implement; it would eliminate the difficulties with auditing that the current xp_cmdshell creates which might make some of the moaners stop moaning. I wonder if they would look at it as a possible future enhancement of SQL Server.

Disabling xp_cmdshell for sysadmins is pretty pointless, because they can just enable it whenever they want it.

Tom

G Peterson SSC Enthusiast Points: 116 More actions · Answer 8

It's easy to go bananas over one single configuration item such as sp_xpcmdshell. Of course noone should enable it wo having a good and well documented case for it.

And just as clear to me, after running systems 10+ years with and without it, I can't say xp_cmdshell per se have caused one single incident.

But we monitor the SQL side closely, we deny anyone that can't justify extensive access, we keep a close dialogue with the Infrastructure/windows team on security making sure they monitor and pamper the underlying OS and network.

Furthermore, it is dangerous to think security is a purely technical thing. Turning of this and that won't make a system secure if some other part of it is sloppy. Of course you have to know what and why you are doing things. Diving into NTFS per se is not dangerous...

We as DBAs must protect data and system, we must to protect personal integrity, and we need to protect the guys that pay our salaries.

Even when they don't know their own best, asking us to implement systems with crap security.

That's when we need to communicate. And with all necessary information given, those who pay our salary have the privilege of either accept putting a potentially dangerous system in a sandboxed environment/scrap it. Or not.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

September 19, 2016 at 7:31 am

#1901622

Just like a gun, it depends on how it's used.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 10

By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

TomThomson (9/19/2016)
Jeff Moden (9/16/2016)
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.
Actually Microsoft fixed that more than a decade ago - if you ensure that the proxy account has no dangerous capabilities then non-SA people can use XP_CMDSHELL harmlessly (that wasn't true when I first encountered XP_CMDSHELL, but it is now); and of course if you don't create a proxy account for XP_CMDSHELL no-one but SQL Server SysAdmins can use it, and I certainly don't want every app that needs cmdshell access from SQL to have to run with SA privilege in SQL Server - that would be just plain crazy - so I'm going to create an XP_CMDSHELL proxy account that provides minimal permissions, unless I'm sure that no apps need to use XP_CMDSHELL. Unless, of course, no apps need it.

Yes, they did fix that long ago, but the fear remains with many people. I use it for my DBA stuff, but then again, my sa login is disabled and nobody has direct permissions to run any extended stored procedure.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

Eric M Russell (9/19/2016)
By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.

The same applies for an attacker who breaks in with sysadmin privs. Hence, the sa login should be disabled and remain disabled.

Maybe I'm just naive, but I see no reason for any application user to have sysadmin privs...ever. I have no doubt that a case could be made for it, but that doesn't mean it'll be implemented in production. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Ed Wagner (9/20/2016)
Eric M Russell (9/19/2016)
By default, only a member of SYSADMIN can execute xp_cmdshell when enabled. If your developers and application users are members of SYSADMIN, they can simply enable xp_cmdshell themselves... and then execute it.
The same applies for an attacker who breaks in with sysadmin privs. Hence, the sa login should be disabled and remain disabled.

I agree 100% with that. I start from the point that there shoul be no SQL logins - windows logins are all that's needed. However, it can happen that apps run on a server in a different domain and inter-domain trusts are not to be set up; iin that case SQL logins are needed, but SA should be given a ridiculously long and messy password, all record of what that password in should be destroyed, and then SA should be disabled. What's the point of that ridiculous password? With luck, it's pointless. If a naive sysadmin falls for some social attack and enables the SA login, the unknown password may be useful if the naive sysadmin doesn't fall for another social attack trying to get him to change SA the password.

Tom

JenMidnightDBA Right there with Babe Points: 787 More actions · Answer 14

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

Our standard response is Sean's blog, "Xp_cmdshell isn't Evil": http://www.midnightdba.com/DBARant/xp_cmdshell-isnt-evil/[/url]

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 15

JenMidnightDBA (9/20/2016)
Peter Schott (9/16/2016)
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.
Our standard response is Sean's blog, "Xp_cmdshell isn't Evil": http://www.midnightdba.com/DBARant/xp_cmdshell-isnt-evil/[/url]

I definitely appreciate that article, but having multiple people giving that opinion (in an article, not a forum thread) goes a long way towards establishing that, especially when there are articles out there, out of date or not, that talk about the evils of xp_cmdshell. 🙂 There's a lot of extra concern because we're dealing with potential HIPAA data and want absolutely no part of any sorts of the fines that could come if there were to be some form of data breach. That's unlikely to happen just due to xp_cmdshell, but those in charge are understandably wary of anything that could increase risk.