The Command Shell

Question

The Command Shell

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
March 29, 2013 at 12:15 am

#291819

Comments posted to this topic are about the item The Command Shell

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

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

chuck.hamilton SSCommitted Points: 1539 More actions · Answer 1

I think the main risk is if an administrator's account becomes compromised.

I myself do not use xp_cmdshell. If I need to script something using the command shell I'll put it in an agent job and run the job.

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

Turning xp_CmdShell off is a useless and futile thing to do. Look at what Chuck.Hamilton wrote above. If an internal or external hacker gets in as "SA", they can get to the command prompt in a totally undectable fashion even if xxp_CmdShell is turned off.

My stance on xp_CmdShell is that it’s no longer a security hole. Rather, malicious use of xp_CmdShell is a symptom of improper security. I’ll also state that turning it off in the face of improper security is a bit like serving drinks at a party to celebrate sobriety.

Consider the following, please.

Who can use xp_CmdShell directly? The answer, of course, is only those people or applications that have “SA” privs or those people that you’ve made the mistake of granting proxy privs to. As Steve suggested, let’s assume that you only allow no “SA” people/apps to execute xp_CmdShell through injection proof (both SQL Injection and DOS Injection) stored procedures and haven’t made the mistake of granting direct execution proxy privs.

Now consider a system where only trusted DBA’s have privs. Is there a benefit to turning xp_CmdShell off? A lot of people insist the answer is “Yes” especially since turning it on is a “logged event”. Have you ever look at what gets logged when you turn it on? On the occurrence of the event is logged in the SQL logs and the default trace will show the machine name and the user that logged in. Nothing is logged about how it was used. Further, a clever person with “SA” privs could create an alternate login, use xp_CmdShell from a different machine, and delete the login when done to cover tracks.

In the case above, turning xp_CmdShell did nothing to prevent the loss of or theft of data. You might not even be able to identify who did it.

Let’s discuss another useless reason for turning xp_CmdShell off even on an unsecure system. Yes, yes, turning it off will keep most honest people away but does it actually enhance security?

The answer is “No”. The system must be secure to prevent a malicious insider or external attacker from simply turning it on. If either or the two types of people I just mentioned have “SA” privs, they don’t even need to use xp_CmdShell to get to the Command Line. They can very simply and quickly create a self-deleting scheduled job to execution “Operating System (Command Line)” tasks and NOTHING get’s logged.

My stance is that, in the face of poor security, turning xp_CmdShell off is like putting a band-aid on a stab wound. People only think that it adds some sort of security and because it may lure some folks into a false sense of security, may actually make them not consider the other incredibly important aspects of proper security.

What IS proper security? The first and most important step is easy. No one and no thing (apps) other than DBAs and system jobs should ever have more than “DBO” privs. If a person or thing doesn’t have “SA” privs, it can’t use xp_CmdShell except through properly configured and constrained stored procedures. If anything else is true, then whomever or whatever has the “SA” privs can get to the Command Line using many different methods and some are absolutely undetectable. Logs created by the methods that do some logging are nothing more than written testimony that the security of the system sucks and that the DBAs didn’t actually do their primary job, that of security.

Considering all of the wonderful things that a DBA (I use xp_CmdShell to {for example} call PowerShell to create system/domain wide disk usage reports) or stored procedure (make some absolutely awesome ETL systems without having to setup SSIS, which is another system that would need to be properly secured) can do and that turning xp_Cmdshell off is a thin veil over rotting meat in the face of poor security, I say that it should never be turned off. Rather, proper security should be implemented, which is what you’re supposed to do, anyway!

{EDIT} What about 3rd party apps that have "SA" privs? First, and I don't use this word a lot, but that's just plain stupid. If the powers that be insist upon it, then put that app on a separate server because that app is a major security hole. You also need to make sure that the SQL Service and Agent on that separate machine have virtually no privs beccause, like I said, anyone or any thing that has "SA" privs can get to the Command Line with the same privs as the SQL Service or Agent in a totally undectable manner whether xp_CmdShell is turned on or off. THAT's the security hole. Not 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)

peleg SSCrazy Points: 2611 More actions · Answer 3

why to use xp_cmdshell when you have the ability to write assembly it NET, more then that, that can be signed and give a higher level of security?

we are not on sql2000, so why the reason to use it?

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

peleg (3/29/2013)
why to use xp_cmdshell when you have the ability to write assembly it NET, more then that, that can be signed and give a higher level of security?
we are not on sql2000, so why the reason to use it?

You're missing the point. It doesn't matter if you have xp_CmdShell turned off and use .NET assemblies instead or not. The real problem is security. If you give users or apps "SA" privs (I strive to only give them PUBLIC privs and make them do most things through stored procedures), you're security is at risk whether you have xp_CmdShell turned off or not.

Using .NET assemblies instead of xp_CmdShell provides no elevated security whatsoever if the users or apps have "SA" privs.

Further, if a properly written stored procedure executes xp_CmdShell, you can give very low prived users the privs to execute that stored procedure (without them having the ability to execute xp_CmdShell directly) and it will be as safe as any .Net assembly you might write with the added benefit that the DBA can maintain it instead of having to manage separately written managed code.

Since only people/apps that have "SA" privs can use xp_CmdShell, turning xp_CmdShell off and not using it is a useless and futile measure because people/apps (attackers) that have "SA" privs can simply turn it back on or use another method around it. If people/apps don't have "SA" privs, it's also useless and futile to turn it off because they can't use it anyway.

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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 5

I agree that if you are vigilant with security then xp_cmdshell is just fine and I've used it for years. However, over the last year or so I've started to turn away from it and not for security reasons. I've been trying to challenge myself to avoid it and find another way to accomplish a task that I would typically use it for. As an example, I setup an agent job recently that compresses some files and copies them to a DR Site and normally I'd use cmd shell to compress the files using PKZip or something but this time I used a powershell function. I didn't want to install any compression program on the production database server and I wanted to avoid a call to cmd shell and I was able to avoid both.

There is a limit to how much effort I'll go through to avoid it, though. I can always use the powershell compression function in other tasks but if I were to find myself having to create all sorts of things to replace the functionality of cmd shell then to me it means making a mess and reinventing the wheel.

Cheers

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

peleg (3/29/2013)
why to use xp_cmdshell when you have the ability to write assembly it NET, more then that, that can be signed and give a higher level of security?
we are not on sql2000, so why the reason to use it?

Let's turn it around? Why write .NET code and go through the time of testing, issues with bugs, deployment across many machines, including in DR situations when xp_cmdshell can allow you the flexibility to handle multiple issues?

I'd also point out you are assuming there are skills in place to do this. Lots of very good DBAs are not .NET developers? do you want them spending time learning this? Or spending their time building code that might be secure? Or downloading something from the Internet and compiling/deploying this to servers?

Enabling the CLR also means enabling is globally, potentially a problem when you don't want unsafe code running in other parts of your system.

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 7

jfogel (3/29/2013)
I agree that if you are vigilant with security then xp_cmdshell is just fine and I've used it for years. However, over the last year or so I've started to turn away from it and not for security reasons. I've been trying to challenge myself to avoid it and find another way to accomplish a task that I would typically use it for. As an example, I setup an agent job recently that compresses some files and copies them to a DR Site and normally I'd use cmd shell to compress the files using PKZip or something but this time I used a powershell function. I didn't want to install any compression program on the production database server and I wanted to avoid a call to cmd shell and I was able to avoid both.
There is a limit to how much effort I'll go through to avoid it, though. I can always use the powershell compression function in other tasks but if I were to find myself having to create all sorts of things to replace the functionality of cmd shell then to me it means making a mess and reinventing the wheel.

I would say Powershell is better if you can call things from a job, but you can't necessarily do that from T-SQL. That's a bit of an issue. I wish we had a ExecPS('') function that would allow calling cmdlets. Course, XP_CMDSHELL does it just fine.

Antares686 SSC Guru Points: 125444 More actions · Answer 8

OK, let's be honest here, bullet proof security models only are good against bullets, but if the hacker throws a grenade instead they can get through. Contrary to what I perceived to be Jeff's statement that a hacker can gain access to the command prompt if they hack the SA account I'd say untrue because they would have to log on to the server itself to access the desktop and you can disable that ability so that the SA can only access the SQL Server. You can make great strides in protecting your system and disabling xp_cmdshell is a great one if you don't need or if you can find a safer alternate method.

It's like developers who build apps and give the user account DBO access then wonder why they were so easy to hack. Duh, you left the door cracked.

All that said though is that disabling xp_cmdshell is just one of the security things you have to worry about and many companies have rigid access controls but often not data security requirements such as all credit cards and sensitive customer information has to be encrypted. Consider what happened with Zappos.com, their system security got breached but they had no data security so boom lots of customers credit cards now exposed.

All in all good security models are like Ogres. Big and scary you say? No, layers. Layer it so that the effort is more costly than the next guy they can try. xp_cmdshell is just a small part of a puzzle you need to navigate on a need by need basis.

My opinion though is avoid it unless you have no alternative and if you don't consider how to layer against it's potential effects to prevent misuse.

TravisDBA SSCoach Points: 15780 More actions · Answer 9

Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Don Burleson wrote a very good article with a real fine example of this that is well worth reading. 😀

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

John Cappelletti Grasshopper Points: 12 More actions · Answer 10

I have XP_CMDSHELL enabled on my staging server only. It enables me to dynamically load a large number source files with various naming conventions and file structures. Like a knife, a useful tool, but should be handled with caution.

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 11

That is a reason I wont simply dismiss the use of cmd shell. My PS function is called from a job and it all worked out for me but there are other projects where I've had to write a report for a client and they want to get the results in to Excel with little to no work involved on their part. These were Crystal reports and they had to look pretty and be able to export the results in to Excel (in a down and across format) which is near impossible. My solution is to use BCP and cmd shell to export the data with headers to a file and call send mail to shoot it off to the recipients provided in a user populated parameter. Works great and the clients think it is slicker than snot.

Your prior response to a post articulated well what I was trying to say in the closing of my initial post in this thread. I don't want to be or include .NET developer(s) when all I want to do is something simple. I don't want to activate CLR or add all kinds of other components just because I can and I don't want developers doing it, either. cmd shell is mature, included and known. All others should be treated with a high level of suspicion just as some treat cmd shell.

Cheers

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

Steve Jones - SSC Editor (3/29/2013)
jfogel (3/29/2013)
I agree that if you are vigilant with security then xp_cmdshell is just fine and I've used it for years. However, over the last year or so I've started to turn away from it and not for security reasons. I've been trying to challenge myself to avoid it and find another way to accomplish a task that I would typically use it for. As an example, I setup an agent job recently that compresses some files and copies them to a DR Site and normally I'd use cmd shell to compress the files using PKZip or something but this time I used a powershell function. I didn't want to install any compression program on the production database server and I wanted to avoid a call to cmd shell and I was able to avoid both.
There is a limit to how much effort I'll go through to avoid it, though. I can always use the powershell compression function in other tasks but if I were to find myself having to create all sorts of things to replace the functionality of cmd shell then to me it means making a mess and reinventing the wheel.
I would say Powershell is better if you can call things from a job, but you can't necessarily do that from T-SQL. That's a bit of an issue. I wish we had a ExecPS('') function that would allow calling cmdlets. Course, XP_CMDSHELL does it just fine.

BWAAA-HAAAA!!!! I CALL PowerShell using 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)

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

TravisDBA (3/29/2013)

Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Tim Burleson wrote a very good article with a real fine example of this that is well worth reading. 😀

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm%5B/quote%5D

I did mention "DOS Injection" that in my first post on this thread but it's absolutely worth mentioning again. Thanks, Travis.

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 14

TravisDBA (3/29/2013)

Another thing to remember here is that script injection is NOT just restricted to SQL. MSDOS commands can be injected in a string that is passed to an xp_cmdshell and executed with the current privileges. If you know how to use ampersands, it's real easy to do. Don Burleson wrote a very good article with a real fine example of this that is well worth reading. 😀

http://www.rampant-books.com/t_super_sql_157_script_injection_msdos.htm%5B/quote%5D

That's a very real hole, and it's one you should beware of. So administrators running things through XP_CMDSHELL shouldn't run anything they do not completely understand, including the parameters.