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

How to call a batch file to execute from an SP Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 3:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Jeff Moden (3/24/2013)
It takes 3ms for an attacker that get's in as "SA" to blow through so called "layering" to execute something using xp_CmdShell because their code is expecting it to be turned off and will turn it on.

And, yes, I whole heartedly agree that the attack. malicious or by accident, frequently comes from within. I'm not blind to that fact. I think, however, you're blinded by the fact that you think disabling xp_CmdShell is a roadblock of any kind. A roadblock is effective only if there's no way around it. It takes no time for someone with "SA" privs to turn it on. Disabling xp_CmdShell lulls people into a false sense of security into thinking that no one can use it. And saying that turning it on is logged is simply saying there will be a documented testimony to bad security.

Stop wasting time ad lulling people into a false sense of security by telling them to turn off xp_CmdShell. It's like telling people that someone could damage the database by using SSIS or Powershell. That's nothing but a veil over rotting meat. Let's get to the real problem. Anything and everything, including a turned off instance of xp_CmdShell, will be used against the systems if someone malicious gains or has access to the server as "SA".

I am sorry, but I feel that you are looking at the exposure through to narrow of a lens, Jeff. It's not just about "blowing through" the layering. Your argument assumes that an attackers only reason to access an instance is to destroy it, which is rarely the case when it comes to internal attacks. It is usually to steal data or intellectual property and do it in such a way as to remain undetected. Enabling xp_cmdshell is an operation that is not so easily done in an undetectable manner, nor is using it. I am not sure how you can think that leaving an open conduit between the database engine and the server's file system, as well as a cmd shell prompt, while running under a different set of credentials than the person running it can be a safe thing to leave lying around.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434702
Posted Sunday, March 24, 2013 3:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Jeff Moden (3/24/2013)
Michael L John (3/21/2013)
I stand corrected.

BUT I also stand by the statement because unfortunately poor security seems to be the norm. It seems as if DBA's are so busy with everything else that security is overlooked.

I will amend the statement to be:
"xp_cmdshell CAN be a security risk"


But it's not. The only people that can use it are people with "SA" privs or if someone was dumb enough to grant a proxy to a user. Anyone with "SA" privs can turn it on even if it's off. It's like saying that using Powershell is a risk. It's not unless a malicious person gets in with the right privs to use it. If a malicious user with the right privs gets into your server, it's not going to matter if you use xp_Cmdshell or not and it sure won't matter if it's turned off or not. The malicious user will simply turn it on.

If the DBAs are too busy to mind their primary job, that of security, then it might be time to have a "come to Jesus" meeting with them. No application and no user should have "SA" privs and THAT's the only way you're going to keep xp_CmdShell from being used maliciously. It's not a risk. It's a symptom of really bad security. You should be able to have stored procedures that use xp_CmdShell up and running just fine and be perfectly secure. If you can't, then the system is at risk even if xp_CmdShell is turned off.

To me, your reasoning is analog to:

Why would I bother locking the doors when I leave my house when a malicious contractor installing a sprinkler system at my home could simply drive the Bobcat Tractor they're using to dig trenches through my front door and take anything they wanted.

No one I know would subscribe to that line of thinking so why apply it to securing a database?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434703
Posted Sunday, March 24, 2013 3:22 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.

--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 #1434705
Posted Sunday, March 24, 2013 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Jeff Moden (3/24/2013)
The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.

Who in their right mind would have a key-hook on the outside if their house


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434707
Posted Sunday, March 24, 2013 4:04 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (3/24/2013)
Jeff Moden (3/24/2013)
The problem is that you only think you're locking the doors by turning off xp_CmdShell. What you forgot to do is to take the keys off the hook next to the doorknob.

Who in their right mind would have a key-hook on the outside if their house


BWAAA-HAAA! Maybe the cousins of the ones that lock the door and leave the key in it?

But that does exemplify the thoughts I have on the xp_CmdShell subject. "Who in their right mind" would think that turning xp_CmdShell provides any kind of improved security at all? Too many people think that turning off xp_CmdShell provides a locked door. While it While it may physically lock the door and require someone with a key to unlock it, too many people either leave the keys close to the door or actually leave the key in the door.

I'm not so much interested in compelling people to use xp_CmdShell. That's up to them. I just don't want anyone to believe for even a micro second that turning of xp_CmdShell does anything substantial to improve security. That's all.


--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 #1434708
Posted Sunday, March 24, 2013 6:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
It is their choice ultimately, but to paraphrase a comment you have made in the past, characterizing xp_cmdshell as "safe as a SELECT statement" is just plain inaccurate. In the spirit of full disclosure, and especially on a public forum, I'll call out the problems with xp_cmdshell every single time and steer people towards more secure, more extensible and more auditable solutions. The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled. I feel like it is irresponsible to portray xp_cmdshell in any other way. To push the idea that as long as only a few people are in the sysadmin Role and there is no Proxy setup that your instance is secure and auditable is simply not true, speaking of lulling people into a false sense of security.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434713
Posted Sunday, March 24, 2013 6:58 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (3/24/2013)
It is their choice ultimately, but to paraphrase a comment you have made in the past, characterizing xp_cmdshell as "safe as a SELECT statement" is just plain inaccurate. In the spirit of full disclosure, and especially on a public forum, I'll call out the problems with xp_cmdshell every single time and steer people towards more secure, more extensible and more auditable solutions. The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled. I feel like it is irresponsible to portray xp_cmdshell in any other way.


No, it's not inaccurate. What is inaccurate is you saying that that turning off xp_CmdShell provides any kind of addditional security in the face of bad security. It just doesn't. Whether it's turned on or off, if someone get's in with "SA" privs, it's going to be a career changing moment for you. People need to realize that there's no benefit to having it turned off because the first thing any attacker, internal or external, is going to do is turn it on.

To push the idea that as long as only a few people are in the sysadmin Role and there is no Proxy setup that your instance is secure and auditable is simply not true, speaking of lulling people into a false sense of security.


Fine. Support your words as I have supported mine. If only few (let's say, 2 DBAs) very trusted individuals have "SA" privs and none of those "individuals" are actually externally outside SQL Server) facing apps (an important point that you've left out that I've emphasized time and again), what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off?


--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 #1434719
Posted Sunday, March 24, 2013 7:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
You're still hung up on 'external attackers.' The point is, xp_cmdshell is a blunt tool that cannot be audited and allows people to run commands as someone else, possibly with more permissions than their own, without the possibility of being detected or tracked. That is not something to be taken lightly and is certainly something most people making decisions about the security of their environment and data would object too if it was fully explained.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434722
Posted Sunday, March 24, 2013 7:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
opc.three (3/24/2013)
The fact is that a system with xp_cmdshell disabled has less security exposures, has less vulnerabilities and is more auditable than a system where it is enabled.


OK.
I'm an intruder on your system.

If I'm connected using non-systemadmin credentials I cannot execute any call to xp_cmdshell anyway, and I cannot get privileges associated with it.
So, it does not really matter if it's disabled or enabled - I won't be able even to figure out that.

Now, if I'm connected as a systemadmin. First thing I will do is
EXEC sp_configure 'xp_cmdshell', 1

Immediately followed by
RECONFIGURE  WITH OVERRIDE

Voilà!
xp_cmdshell is enabled, no matter what state it was 3 ms ago.

So, where those promissed "less security exposures, has less vulnerabilities"?
Post #1434723
Posted Sunday, March 24, 2013 7:33 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
opc.three (3/24/2013)
The point is, xp_cmdshell is a blunt tool that cannot be audited and allows people to run commands as someone else, possibly with more permissions than their own, without the possibility of being detected or tracked.


Would it be wiser to limit the privileges associated with the account running sqlserver service to its jon related tasks?
Read from there, write there, check on that location, execute that task.
That's it. The list is closed.
If you need to do something else - talk to your system administrator, as they say in MS messages.

This layer would be definitely harder to pass than to enable xp_cmdshell, don't you think?


Post #1434725
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse