Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Command Shell Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 12:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
Comments posted to this topic are about the item The Command Shell






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1436833
Posted Friday, March 29, 2013 6:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:40 AM
Points: 46, Visits: 215
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.
Post #1436897
Posted Friday, March 29, 2013 6:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1436900
Posted Friday, March 29, 2013 7:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 123, Visits: 513
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?
Post #1436908
Posted Friday, March 29, 2013 7:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1436910
Posted Friday, March 29, 2013 7:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:27 AM
Points: 349, Visits: 903
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
Post #1436928
Posted Friday, March 29, 2013 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1436976
Posted Friday, March 29, 2013 9:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1436980
Posted Friday, March 29, 2013 9:47 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Wednesday, March 26, 2014 3:38 PM
Points: 8,369, Visits: 732
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.



Post #1436991
Posted Friday, March 29, 2013 9:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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. ..."
Post #1436993
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse