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 ««1234»»»

How to prevent ANY use of xp_CmdShell? Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 3:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
opc.three (5/6/2013)
Jeff Moden (5/6/2013)
opc.three (5/6/2013)
At the Windows level I think you can intercept and prevent attempts by the sqlservr.exe process to spawn instances of cmd.exe. I am guessing this is done using WMI but am not sure which Windows subsystem is used to do it. It is something that exists in the environment where I am currently, but I have very little details around what is seen by that process (I doubt it can know which member of sysadmin ran it through SQL Server since that is an application domain away) or how effective it is.


Thanks, Orlando. Any way you could find out more about the environment you currently work in for that? And you actually seen it stuff a request from xp_CmdShell? That would be the proof of the pudding especially since that would also stuff requests by OPENROWSET and a couple of other avenues that people with "SA" privs have to get to a command line.

I can't ask too many of those types of questions around here, if you know what I mean. I know there is a WMI Event for "process creation" we can listen for. If there is synchronous access in the context of "pre-execution" and the event payload includes the source process then it should be pretty easy. I'll try to work up a proof-of-concept myself, and see what I come up with.


Very cool and understood on the "ask too many questions" thing. It'll be interesting to see what you can come up with and whether or not it messes with any of the base functionality of SQL Server such as the deletion of old log files.


--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."

(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 #1449913
Posted Monday, May 6, 2013 4:19 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
By Jove! I may have found the solution. Let me do some testing before I release the information.

--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."

(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 #1449924
Posted Tuesday, May 7, 2013 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
With the help of this article I was able to work up a Console App in C# using VS 2012 to alert me when a specific type of process (cmd.exe) was started, was modified or was deleted. I am not versed in the ins and outs of the Win32_Process WMI class so could not figure out how to know if the process was spawned from sqlservr.exe or not, but my guess is that it is possible given the existence of a property I found named IsParentProcessIdNull.

My code, including the WMI stub-class generated on my Windows 8 machine using the VS 2012 tools, is attached as a rar archive.

Here is a preview of the output while the program was running and I opened a cmd.exe window:



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
WmiProcessWatcher.rar (1 view, 12.35 KB)
5188b96a.jpg (254 views, 113.08 KB)
Post #1450182
Posted Wednesday, June 12, 2013 7:50 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
Ok, Orlando... I'd really be interested in how that bit of fine code could be modified to reject usage of cmd.exe by a given user because I just saw a video of a hacker making his way to the registry and undoing some supposedly safe methods.
http://www.securitytube.net/video/653

He used VBA but he could have just as easily been in SQL Server as an "SA" to sp_regwrite to do the same thing. That means his attack software would take 3ms to try turning xp_CmdShell on and going to the command prompt and maybe another 4ms for his attack software to recognize the failed attempt and make a trip to the registry to correct the "problem" so that he could get to the cmd.exe program using xp_CmdShell.

It also turns out that this supposedly safe method has some pretty nasty caveats for us users...


Your method (the code you posted in the post above this one) seems like it might be better if you could demo how to reject usage attempts.

Still, it seems that a determined hacker that can get to the registry through SQL Server can find and undo just about anything. If you want to prevent someone from using the command line from SQL Server, merely turning off xp_CmdShell seems like pissing on a forest fire. The real key is to keep people from getting in as "SA".


--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."

(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 Attachments 
PreventAccessToCommandPrompt.png (204 views, 21.28 KB)
Post #1462882
Posted Thursday, June 13, 2013 2:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Jeff Moden (6/12/2013)
Ok, Orlando... I'd really be interested in how that bit of fine code could be modified to reject usage of cmd.exe

It cannot. I was only showing that it is trivial to start listening to Windows events. Intercepting the process creation is a little more involved, but possible. Think anti-virus software. Every file added or modified and every process created on a system running anti-virus software goes through some heuristic checks before the anti-virus software decides whether that data or action is going to be allowed to continue unfettered. Same principle here.

Now I am no Windows programmer, don't want to become one, but I know enough about WMI to be a little dangerous here. From some light reading online it may take a little more than WMI even. Like I said, I am not a Windows programmer.

The point is, it is possible to block processes from being created. My requirements if I was asking a qualified Windows programmer to do this would be to disallow process creation if the parent process were sqlsrvr.exe and the child process were cmd.exe, or something similar that would be analogous to running something in a cmd shell. That might require some research. All I know is that it is possible to do, and that there are some third-party tools out there that do it. I do not know which third-party tools, but know that I get calls when I stand up a new server and start running xp_cmdshell (as part of legacy apps I support, not new development) because it trips the monitoring software. I end up having to file for a "security exception" for each server so the apps can run xp_cmdshell.

by a given user because I just saw a video of a hacker making his way to the registry and undoing some supposedly safe methods.
http://www.securitytube.net/video/653

He used VBA but he could have just as easily been in SQL Server as an "SA" to sp_regwrite to do the same thing. That means his attack software would take 3ms to try turning xp_CmdShell on and going to the command prompt and maybe another 4ms for his attack software to recognize the failed attempt and make a trip to the registry to correct the "problem" so that he could get to the cmd.exe program using xp_CmdShell.

This assumes the SQL Server service account has permissions to write to the registry outside of the normal SQL Server keys that it should have access too, and that should not be the case if the SQL Server was locked down and was using a low-privileged service account.

It also turns out that this supposedly safe method has some pretty nasty caveats for us users...

As I understand it "interactive command prompt" has special meaning and leans towards an interactive user, i.e. someone logged into the console or a RDP session, trying to get to a command prompt. As far as I know these types of settings would not apply to services like SQL Server.

Still, it seems that a determined hacker that can get to the registry through SQL Server can find and undo just about anything.

Again, only if the SQL Server service account allows it.

If you want to prevent someone from using the command line from SQL Server, merely turning off xp_CmdShell seems like pissing on a forest fire. The real key is to keep people from getting in as "SA".

That's not good enough. It's obviously uber important to limit the list of sysadmin Members, and to do your very best to weed out the untrusted, but it's not the end-all be-all. It can't be. We're dealing with humans here Jeff. Whatever roadblocks can be stood up, including working towards disallowing xp_cmdshell use via whatever means we have available to us, and to whatever extent you need to take it in your environment. At the end of the day though no one really needs xp_cmdshell. There are other much better tools that overlap 100% of it's functionality in more secure and robust ways, and so it's too big of a risk to have it just lying around waiting to be abused.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1462934
Posted Thursday, June 13, 2013 7:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
I agree with almost everything you said except the last part. I don't consider using SSIS (for example) to be a "more robust" way of doing things. More complicated, more costly, and requiring different skills, yes. More robust... well, that's only in the eyes of the beholder. Heh... I guess that's a bone of contention we'll never resolve between us.

Still, if a company wants to really limit the damage that can be done if someone does get in with "SA", then I absolutely agree with everything you've said. Guess I'll have to find a nice Windows forum and see if someone can come up with a method similar to the detection code you were kind enough to provide. Yep... I'm still interested in doing this (even though I'd probably never implement it on my machines) because just disabling xp_CmdShell provides no non-trivial safety. It's just too easy for someone with "SA" privs to reverse it or get around it using an EXEC task, OPENROWSET, etc.

Oh... and the "interactive command prompt" that they were referring to is actually cmd.exe. It will be interesting, once we find a way of disabling it for SQL Server, to see if such things as backups still work.


--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."

(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 #1463060
Posted Thursday, June 13, 2013 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Jeff Moden (6/13/2013)
I agree with almost everything you said except the last part. I don't consider using SSIS (for example) to be a "more robust" way of doing things. More complicated, more costly, and requiring different skills, yes. More robust... well, that's only in the eyes of the beholder. Heh... I guess that's a bone of contention we'll never resolve between us.

"More complicated" is a subjective opinion.

"More costly"? How? SSIS costs $0. It is included in the SQL Server product at no extra cost. If you want to say it adds maintenance costs over xp_cmdshell, you would be wrong. Objectively speaking SSIS costs the same amount of money to start using it as xp_cmdshell. When installing SSMS on your development machines, decide to select the check box to add "BIDS" (2005, 2008) or "SSDT" (2012) get the development tools. To add it to a server, during installation select the "Integration Services" check box. That's it, and in the case of the server you do not even need the have service running in Windows to leverage the functionality so it doesn't take away system resources at all. With xp_cmdshell, you do not have to select anything during install (unfortunately) but you do have to enable it within the instance by running sp_configure in SSMS. Objectively I say it's a wash to "install" and begin using each feature.

Objectively speaking, SSIS is more robust than xp_cmdshell. Have you seen the documentation for SSIS? It's endless. Did you know it's extensible too? If there is something SSIS won't do and I want to can it so others can use it without copying and pasting a bunch of code into a Script Task every time, I can write new SSIS Components in .NET and people can install those on their machines. Here is one that allows us to run PowerShell code natively within an SSIS Package, pretty cool. How is the documentation for xp_cmdshell? One web page. You cannot really make the argument that xp_cmdshell offers anything over and above what xp_cmdshell does.

Since you're throwing subjective comments out left and right, I do have one for you: xp_cmdshell is a shitty tool. It's clear you're just being obstinate. Sorry, that was two.

It's just too easy for someone with "SA" privs to reverse it or get around it using an EXEC task, OPENROWSET, etc.

I already showed that this trick you uncovered from that Chinese site does not work on 64-bit installations of SQL Server, of which the overwhelming majority of production instances must be by now. This is not to mention that in order for this to work on a 32-bit instance you need to know of an existing or be able to drop an Access mdb file onto the file system that the SQL Server service account can access, which on NT4 was a given (yes, you're referencing a 15 year old hack) but is not a foregone conclusion anymore. It's not a relevant vulnerability anymore Jeff so you should probably give up pointing to it as a reason to continue using xp_cmdshell.

Oh... and the "interactive command prompt" that they were referring to is actually cmd.exe. It will be interesting, once we find a way of disabling it for SQL Server, to see if such things as backups still work.

Like I said, there is special meaning given to "interactive" when it comes to a session, i.e. the OS knows when someone is logged in trying to run something and something is being run by an unattended service like SQL Server.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1463140
Posted Saturday, June 15, 2013 10:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
My reference to "More costly" is because, if you install it on a different box, you have the cost of the different box and licensing changes (IIRC). If you install it on the same box, then the "cost" is contention with the main database. Either way, you have a different "system" to maintain.

As to documentation, there's tons of documentation for xp_CmdShell in the same place that there is for PowerShell... on the internet.

ALL of our conversations have been both subjective and highly preferential. There's nothing wrong with that.

The most important thing that I want people to know is that, whether you use xp_CmdShell or not, just disabling it will not prevent any attacker from using it nor, in its absence, will it prevent an attacker from getting to the command prompt with elevated privs. Just because the 15 year old hack using OPENROWSET might not work anymore (and I still haven't tested that to be sure, but will), doesn't mean that a dedicated hacker (and they are VERY didicated) can't come up with another method if they can get into the server as "SA". THAT's what must be done... you MUST prevent unauthorized people from getting in as "SA". Anything less is nothing more than a futile effort.

And don't ever send me a foul 4 letter word PM like you recently did. You complained about Sergiy being rude. Try to follow your own advice.


--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."

(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 #1463866
Posted Saturday, June 15, 2013 10:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Jeff Moden (6/15/2013)
My reference to "More costly" is because, if you install it on a different box, you have the cost of the different box and licensing changes (IIRC). If you install it on the same box, then the "cost" is contention with the main database. Either way, you have a different "system" to maintain.

As to documentation, there's tons of documentation for xp_CmdShell in the same place that there is for PowerShell... on the internet.

ALL of our conversations have been both subjective and highly preferential. There's nothing wrong with that.

You might see it that way, but that's not surprising. There are real security and auditing vulnerabilities introduced when xp_cmdshell is enabled and sanctioned for use within an environment. That's a fact.

The most important thing that I want people to know is that, whether you use xp_CmdShell or not, just disabling it will not prevent any attacker from using it nor, in its absence, will it prevent an attacker from getting to the command prompt with elevated privs. Just because the 15 year old hack using OPENROWSET might not work anymore (and I still haven't tested that to be sure, but will), doesn't mean that a dedicated hacker (and they are VERY didicated) can't come up with another method if they can get into the server as "SA". THAT's what must be done... you MUST prevent unauthorized people from getting in as "SA". Anything less is nothing more than a futile effort.

False. There are inherent risks in having it enabled and sanctioning its use, as opposed to putting as many barriers in between it and a malicious user. Like I said, and showed, detecting the starting of a cmd shell is possible at the Windows level. If you're clinging to the idea that because you can't block it from being enabled in SQL Server as a reason to use it, give it up.

And don't ever send me a foul 4 letter word PM like you recently did. You complained about Sergiy being rude. Try to follow your own advice.

One good turn deserves another Jeff. You started this one.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1463873
Posted Saturday, June 15, 2013 10:48 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 36,978, Visits: 31,498
opc.three (6/15/2013)

One good turn deserves another Jeff. You started this one.


Absolute rubbish, Orlando. I've not called you any names nor used the kind of language you've used in the PM. I fact, I've appluaded your efforts to make things more secure despite the fact that I disagree with you.


--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."

(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 #1463879
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse