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, June 15, 2013 11:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
Jeff Moden (6/15/2013)
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, get real man. I never called you any names and you were in the Navy for goodness sake. That's why I emailed your personal email and did not air it out publicly. But I guess you want to try to drag me out here and look to embarrass me with an attempt at taking the high road. This after I tried to talk to you directly regarding an honest response to you proposing xp_dirtree to resolve an SSIS problem that somehow you saw a path to start up with xp_cmdshell again. Ridiculous man.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1463880
Posted Saturday, June 15, 2013 11:22 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
That's enough kids.
Post #1463883
Posted Saturday, June 15, 2013 3:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
I have a theory about this: it says something along the lines of 'make sure that "XP_cmdshell" uses a windows login without much privilege'. I can't remember whether this works or not against a competent attacker - I do know that it worked well enough for me, and I had some people whose job included being a competent attacker.

Of course you may want to run SQL Server under a domain admin user and make the SQL server command proxy (or whatever it is called now) a domain admin, in which case what I suggested above is utterly pointless.


Tom
Post #1463904
Posted Sunday, June 16, 2013 10:06 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,950, Visits: 30,231
opc.three (6/15/2013)
Jeff Moden (6/15/2013)
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, get real man. I never called you any names and you were in the Navy for goodness sake. That's why I emailed your personal email and did not air it out publicly. But I guess you want to try to drag me out here and look to embarrass me with an attempt at taking the high road. This after I tried to talk to you directly regarding an honest response to you proposing xp_dirtree to resolve an SSIS problem that somehow you saw a path to start up with xp_cmdshell again. Ridiculous man.


Heh... read my post. I didn't say you called me any names. Normally it takes some name calling to get a PM like that from someone and I'm just making sure that you're aware that I've called you no names. It's also presumptuous to think that getting such a PM in the middle of a heated debated would be enjoyed by anyone (especially from a friend), even those of us that were in the Navy. Using words like "xp_CmdShell is a s-----y tool" also implies that I'm a "s-----y" Developer and I take offense to it much like you took offense to Sergiy's use of the word "Cowboy" and other words. It wasn't directed at you and you didn't direct "s-----y" at me, but we are both offended, none the less.

You have insinuated that my point of view (and, therefor, me) is ridiculous and unreal (heh... you managed to do both in the post above). I could say the same of you because, until they make an option to permanently disable xp_CmdShell from outside of SQL Server, no argument for disabling it will hold water against an "SA" level attack and, yet, you press on with that notion. Whether we agree or not (and I know we never will and that's OK) on the use of xp_CmdShell, both of our goals should be to warn people about what can be done during an "SA" attack even if xp_CmdShell is disabled.

As for the response proposing the use of xp_DirTree shifting to xp_CmdShell (and you and I obviously think differently on the subject), I actually believe that using xp_CmdShell is safer than using xp_Dirtree because xp_DirTree IS undocumented and could go away or change at any time just as xp_GetFileDetails did. I also believe that having SSIS Developers writing file handling scripts in VB or whatever to be executed from SSIS, is no safer than having an SQL Developer doing the same with a different tool but at least I'll have the opportunity to review the T-SQL solution as a DBA.

As a side bar, it would be nice if MS would realize that both their flagship ETL product and database engine tools are sorely lacking in many areas thus requiring the use of such ancillary scripts and other excursions to the operating system including the use of unsupported but useful extended stored procedures. One should not have to know VB, C#, DOS, or PowerShell (or any of a dozen other scripting languages such as PERL) nor write custom programming (SQLCLR) to get things done between files and the database in an RDBMS system that has been around for more that 2 decades in one form or another.

Shifting gears back to what's really important, neither of us have been saints in our discussions on this subject. Let's renew our friendship and both of us change our tone on the subject even as we continue to agree to disagree so that we can explain to people that even if you decide to not use xp_CmdShell, you're still not safe just by disabling it. Other steps must be taken. Both the friendship and the message are worth saving.


--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 #1463967
Posted Monday, June 17, 2013 8:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
Heh... read my post. I didn't say you called me any names. Normally it takes some name calling to get a PM like that from someone and I'm just making sure that you're aware that I've called you no names. It's also presumptuous to think that getting such a PM in the middle of a heated debated would be enjoyed by anyone (especially from a friend), even those of us that were in the Navy.

If you refer back to the timeline (minus your spam filter getting in the way, which was quite funny by the way) we were not technically in the midst of a heated debate when I in fact emailed you, at least not on the specific thread that prompted my response. It was right after this post from you.

All I said was that xp_dirtree was undocumented, which it is, and I then proceeded to provide a solution to the OP that was consistent with 1. What they were asking for, and 2. The Forum it was posted in. If you thought xp_cmdshell was the best solution to the OP’s problem then why not propose it in the first place? At least if you had done that it could have been taken as an honest attempt to help them using the tools you prefer and I would probably have offered my VB code and left it alone. But instead, by throwing it out there as a rebuttal to my comment, secondary to proposing a different solution, it became clear you were attempting to re-kindle a debate whose destination we both are overly familiar with. That’s why I emailed asking, and I quote, "seriously, what the f**k is your problem?" For me, that’s a perfectly normal thing to ask a friend when motives appear devious and the signal is unclear, so sue me for trying to clarify by leading in with some plain language…I felt it was warranted.

Using words like "xp_CmdShell is a s-----y tool" also implies that I'm a "s-----y" Developer and I take offense to it much like you took offense to Sergiy's use of the word "Cowboy" and other words. It wasn't directed at you and you didn't direct "s-----y" at me, but we are both offended, none the less.


I guess I’m saying you’ve made a bad design choice by siding with employing xp_cmdshell, and thereby opening the door for abuse both within and outside any applications that might safely expose it initially. I’ve made bad design choices. Anyone who has done a non-trivial amount of design work in this field has. I’m not saying you’re a shitty developer, I’m saying you’re human. Call it condescending if you like but that’s my honest opinion based on my experience. You’re my senior in lots of areas of SQL Server development but in this one I think you continue to make the mistake of siding with xp_cmdshell. Calling xp_cmdshell a s****y tool is backed by lots of empirical knowledge as well as lots of facts. Apologies go out to the original developers of it if my comments are offensive but that’s how I see it. At the time it was developed it was probably a great addition and may have even entered the product with much fanfare but now there are more robust and more secure ways of interacting with SQL Server, and the Windows host on which it is running, and I would love to see you exercise some of your options.

You have insinuated that my point of view (and, therefor, me) is ridiculous and unreal (heh... you managed to do both in the post above). I could say the same of you because, until they make an option to permanently disable xp_CmdShell from outside of SQL Server, no argument for disabling it will hold water against an "SA" level attack and, yet, you press on with that notion. Whether we agree or not (and I know we never will and that's OK) on the use of xp_CmdShell, both of our goals should be to warn people about what can be done during an "SA" attack even if xp_CmdShell is disabled.

I am sorry you feel that way. I do warn people about keeping strict control over the member in their sysadmin Role any time the context compels me. Same goes with warning people about the downsides and pitfalls of using xp_cmdshell. In a vacuum, I will give you your point about being able to implement it within an application such that someone with only public Role membership can execute a proc that calls xp_cmdshell. My problem with your argument is that you only address that one attack vector. The rest of your argument seems to go like this: if someone has sysadmin-level privileges there is nothing you can do to block them from using of xp_cmdshell worth doing so you might as well leave it enabled and not bother trying to block its use at all; just work on trusting all your sysadmin members unconditionally. I think that is an apathetic approach to security and laden with problems that in any non-trivially sized team leaves the organization wide open to potential abuse.

As for the response proposing the use of xp_DirTree shifting to xp_CmdShell (and you and I obviously think differently on the subject), I actually believe that using xp_CmdShell is safer than using xp_Dirtree because xp_DirTree IS undocumented and could go away or change at any time just as xp_GetFileDetails did.

Then why didn’t you propose xp_cmdshell in the first place?

I also believe that having SSIS Developers writing file handling scripts in VB or whatever to be executed from SSIS, is no safer than having an SQL Developer doing the same with a different tool but at least I'll have the opportunity to review the T-SQL solution as a DBA.

Here’s my take on that: I don’t think you know enough about how to configure and use SSIS (or PowerShell) to say that with any level of certainty.

As a side bar, it would be nice if MS would realize that both their flagship ETL product and database engine tools are sorely lacking in many areas thus requiring the use of such ancillary scripts and other excursions to the operating system including the use of unsupported but useful extended stored procedures. One should not have to know VB, C#, DOS, or PowerShell (or any of a dozen other scripting languages such as PERL) nor write custom programming (SQLCLR) to get things done between files and the database in an RDBMS system that has been around for more that 2 decades in one form or another.

It’s clear that is never going to happen, despite the fact that I wish it would. This Connect item is one that I point to often as anecdotal evidence of that, as well as the choices made in the Azure platform implementation and a general move towards distributed cloud-based computing. I especially like the points the person who posted on 6/17/2012 at 2:18 PM made. I want tools like BULK EXPORT to be added to the system too, but not for the reasons you do. I want them added because it makes the product more complete which makes my job easier when I recommend switching from Oracle or not switching to MySQL. I also want it because it would afford folks one less reason to enable and use xp_cmdshell. In the meantime I choose to find alternate ways to get data in and out of SQL Server without accessing the command line from within T-SQL, these days an unforgivable offense from a design perspective in my opinion.

Shifting gears back to what's really important, neither of us have been saints in our discussions on this subject. Let's renew our friendship and both of us change our tone on the subject even as we continue to agree to disagree so that we can explain to people that even if you decide to not use xp_CmdShell, you're still not safe just by disabling it. Other steps must be taken.


I have always agreed with the idea that alone disabling xp_cmdshell does not a secure system make. I think where we might differ is that for me, disabling xp_cmdshell is a step on the road to a secure a SQL Server, whereas for you it doesn’t seem to make one bit of difference.

Both the friendship and the message are worth saving.

I couldn’t agree more. I think both our messages can continue to help people in certain contexts even while they are at bitter odds with each other. I hope we get to meet in person someday. Happy posting friend.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1464442
Posted Tuesday, June 18, 2013 12:10 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,950, Visits: 30,231
If you refer back to the timeline (minus your spam filter getting in the way, which was quite funny by the way) we were not technically in the midst of a heated debate when I in fact emailed you, at least not on the specific thread that prompted my response. It was right after this post from you.


THAT's what prompted your message? I guess I shouldn't be surprised but I really am. Did you ever fill in the rest of the solution you posted?

Shifting gears, you are correct, I've only seen enough of SSIS by helping SSIS developers fix some problems to know that I don't like it for most things. A quote from one of the lines in that connect item sums up my opinion of it. "Using a massive ETL package like SSIS to simply dump a table to flat file is most certainly not a simple solution." I was glad to see you make the comment you did on that particular item. I had stopped following it after the last comment I made on it. Like you, I think MS made a huge mistake in closing that item as "Will Not Fix".

Of course, except for the last statement in your thread above (which I was very pleased to see), I disagree with pretty much everything else you said for reasons I've previously stated so I'll not waste your time arguing the point any further on this thread.


--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 #1464463
Posted Tuesday, June 18, 2013 12:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:56 AM
Points: 200, Visits: 778
I am a big fan of it but I am beginning to think that SSIS is possibly the root of all evil...

'Only he who wanders finds new paths'
Post #1464834
Posted Friday, September 13, 2013 9:15 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,950, Visits: 30,231
david.alcock (6/18/2013)
I am a big fan of it but I am beginning to think that SSIS is possibly the root of all evil...


Apologies for missing this post, David. Having no great love for SSIS myself, I have to ask why you're beginning to think that way.


--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 #1494774
Posted Wednesday, October 23, 2013 6:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:11 AM
Points: 206, Visits: 756
If you were a hacker with SA access on a SQL Server where someone managed to completely lock out xp_cmdshell, wouldn't it just be as efficient to load some unsafe CLR and do whatever you wanted to do with xp_cmdshell?

I would guess that any exploit someone could use with xp_cmdshell would be even easier to pull off with a good old piece of C# code and some P/Invoke.

My conclusion would be the same as your, if someone manage to be SA, they win (not a reason to not try to make their life miserable though).
Post #1507573
Posted Wednesday, October 23, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
I am not sure if it was this thread, or some other related thread, but I have the same comment about the CLR as well as some other features of SQL Server. We should have the ability to leave them uninstalled, not just disabled, for the reason you mentioned as well as some others.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1507633
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse