June 13, 2013 at 7:02 am
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
Change is inevitable... Change for the better is not.
June 13, 2013 at 8:59 am
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
June 15, 2013 at 10:02 am
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
Change is inevitable... Change for the better is not.
June 15, 2013 at 10:21 am
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
June 15, 2013 at 10:48 am
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
Change is inevitable... Change for the better is not.
June 15, 2013 at 11:01 am
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
June 15, 2013 at 11:22 am
That's enough kids.
June 15, 2013 at 3:37 pm
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
June 16, 2013 at 10:06 am
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
Change is inevitable... Change for the better is not.
June 17, 2013 at 8:58 pm
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
June 18, 2013 at 12:10 am
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
Change is inevitable... Change for the better is not.
June 18, 2013 at 12:41 pm
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'
September 13, 2013 at 9:15 pm
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
Change is inevitable... Change for the better is not.
October 23, 2013 at 6:15 am
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).
October 23, 2013 at 8:07 am
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
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply