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 Saturday, May 4, 2013 3:31 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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, is there any method available that will absolutely prevent someone with "SA" privs from using xp_CmdShell?

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

(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 #1449475
Posted Saturday, May 4, 2013 6:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,388, Visits: 9,604
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.
Post #1449482
Posted Saturday, May 4, 2013 9:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 PM
Points: 21,351, Visits: 15,032
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1449487
Posted Saturday, May 4, 2013 10:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(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 #1449495
Posted Saturday, May 4, 2013 11:01 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(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 #1449496
Posted Saturday, May 4, 2013 11:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 PM
Points: 21,351, Visits: 15,032
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1449497
Posted Sunday, May 5, 2013 4:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,388, Visits: 9,604
SQLRNNR (5/4/2013)
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.



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.
Post #1449503
Posted Monday, May 6, 2013 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 7,094, Visits: 12,583
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
Post #1449807
Posted Monday, May 6, 2013 2:03 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(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 #1449869
Posted Monday, May 6, 2013 3:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 7,094, Visits: 12,583
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
Post #1449910
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse