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