How to prevent ANY use of xp_CmdShell?
-
Please forget whether or not you're pro or con on the subject of the use of xp_CmdShell for just a minute. Thanks in advance for that. 🙂
On to the question...
I'm not a Windows Admin by any means and I've never been the one responsible for Policy-Based Management anywhere. I need a way to prevent anyone with "SA" privs on SQL Server from using xp_CmdShell. My searches on the internet have been mostly fruitless and the folks I work with aren't sure it can even be done. I've found a lot of articles on how to monitor whether it is on or off by using tools such as PBM (Policy-Based Management) but nothing on how to absolutely prevent it (xp_CmdShell) from being turned on. I did find one article (http://nujakcities.wordpress.com/2012/09/21/common-sql-server-security-mistakes/) where the author suggested that PBM could be used to prevent xp_CmdShell from being turned on but there were no details on how to accomplish such a thing.
To simplify the question, [font="Arial Black"]is there any method available that will absolutely prevent someone with "SA" privs from using xp_CmdShell[/font]?
Thanks folks. I really need some help on this.
--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) -
Can you deny access to the windows account linked to sql server? That's how folders / files access works. .cmd is just an exe file AFAIK.
If the file is part of the sql server install (which I doubt it is), you could just delete the file from the folder. Obviously don't delete the windows version, plenty of things still require it for the server to run correctly.
No idea what happens if someone tries to activate access and run xp_cmdshell. That might get you anything from an error message to full catastrophic failure.
-
There is a Surface Area Configuration facet in PBM as well as a Server Configuration Facet. Both of these have the XPCmdShell check to see if it is enabled. But neither of these facets (when applied to a condition and used in a policy) can prevent the change of the server configuration. These facets are designed to report on configurations that are out of compliance and not prevent them.
A good alternative to preventing is to have a policy in place that it is not to be used unless otherwise documented. Then audit for the use of xp_cmdshell. When somebody uses it, then you have a log of the use and the individual can be spoken to.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (5/4/2013)
There is a Surface Area Configuration facet in PBM as well as a Server Configuration Facet. Both of these have the XPCmdShell check to see if it is enabled. But neither of these facets (when applied to a condition and used in a policy) can prevent the change of the server configuration. These facets are designed to report on configurations that are out of compliance and not prevent them.That's spot on with what I was finding. Thank you very much for the confirmation.
A good alternative to preventing is to have a policy in place that it is not to be used unless otherwise documented. Then audit for the use of xp_cmdshell. When somebody uses it, then you have a log of the use and the individual can be spoken to.
I'm not sure that's true. An "SA" could manipulate the logs or even avoid the logs by creating a self deleting job that uses CmdExec. It may not be xp_CmdShell but it does get you in with some pretty elevated privs.
The truth is, I'm not so worried about folks on the inside. They have to go through a pretty rigorous background check. Hackers aren't going to login as themselves. They're going to login as someone else (perhaps even as "SA"), get what they need, delete parts of the log (saw it done in a recent hacker demo. The guy was good and his software was even better), and be out with no one the wiser. He even showed us how to reinstate the xp_CmdShell extended stored procedure (2 different ways and said there was more) if someone deleted it.
After the demo I saw, turning off xp_CmdShell seems to be like trying to put out a 4 cord bonfire by spitting on it. 😛 That's what led me to ask the question if PBM or any other tool could prevent someone with "SA" privs from using xp_CmdShell. It looks like the ONLY way to keep a hacker from using it is to not let them in as "SA". Of course, that's the real goal but was looking for that extra "layer of protection" that some folks think they get just by turning it off. Post mortum logs just don't get it for me.
Anyway, thank you again for your time. I really appreciate it.
--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) -
Ninja's_RGR'us (5/4/2013)
Can you deny access to the windows account linked to sql server? That's how folders / files access works. .cmd is just an exe file AFAIK.If the file is part of the sql server install (which I doubt it is), you could just delete the file from the folder. Obviously don't delete the windows version, plenty of things still require it for the server to run correctly.
No idea what happens if someone tries to activate access and run xp_cmdshell. That might get you anything from an error message to full catastrophic failure.
Thanks Remi. I know you could delete the file back in SQL Server 2000 but I don't believe you can do that and still have an operational system anymore. Although I love the idea of denying the SQL Server Service login access to the file as you suggest, I don't know what will happen to such things as backups and backup file deletions. I'll have to try that on a test server.
Thanks for the ideas. I appreciate your time.
--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) -
I have seen those demos as well. What I think the takeaway from them is
1. Logging should be stored on a different system
2. Monitors should be in place (HIPS) to alert and prevent
3. Security team that is on top of things.
But in the end, if the hacker is good enough, then they can get in and out still. Once they get onto the server, they will get what they want. Think about it, if you have your network firewalled, zoned, routing rules in place, SQL Servers on a separate subnet with a separate firewall acl, Host Intrusion Detection and Host Intrusion Prevention systems in place - the hacker can put xp_cmdshell on the server even if you delete the dll.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (5/4/2013)
I have seen those demos as well. What I think the takeaway from them is1. Logging should be stored on a different system
2. Monitors should be in place (HIPS) to alert and prevent
3. Security team that is on top of things.
But in the end, if the hacker is good enough, then they can get in and out still. Once they get onto the server, they will get what they want. Think about it, if you have your network firewalled, zoned, routing rules in place, SQL Servers on a separate subnet with a separate firewall acl, Host Intrusion Detection and Host Intrusion Prevention systems in place - the hacker can put xp_cmdshell on the server even if you delete the dll.
Not without access to internet,
but yeah at some point, you're just screwed...
I still think you can play with the windows permissions. There's a way to forbid installing stuff, no idea what the consequences would be assuming it's possible.
-
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
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.
--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 (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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
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.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) -
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.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) -
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 -
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.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 (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.exeIt 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
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply