Does anyone have a good reason to run xp_CmdShell?

Not at all , Infact I'll be glad if to know if theres a better way of doing this.

What i have setup is as follows

A local machine with SQL installed which has linked server setup to a few instances I manage. I use xp_cmdshell on my local box to ping the instance IPs at the time a ssrs report is executed ( just so that I know the server is online when the report is executed) I dont store the data anywhere and all queries are at run time , i also use it check if there are file in particular location etc. other things include perfom , xp_fixeddrives, job status , database status etc

Since I am using it on my local instance and the servers are not prod servers , it made using xp_cmdshell just easier.

It sounds like you're driving towards the "someone has to have the keys to the kingdom" debate. I agree you can only do so much and at the end of the day at least one human has to be trusted and we know how we are. That said, there are things one can do through system and application design that limit which humans can handle the keys. I am certainly no expert, however for me xp_CmdShell is not worth enabling or using.

I choose not to use SQLCLR for accessing the file system. If I find myself in a situation where I want to retrieve some information about a directory or file from the file system while in T-SQL code then I take a step back or to the side and move in a different design direction.

Where I previously would have looked to CmdShell I now look to PowerShell...not that it has anything to do with security, just felt like plugging PowerShell again 🙂

Application developers are not tasked with writing PowerShell scripts that perform file system maintenance tasks on the server hosting the database engine so no worries there. Those scripts are written by DBAs or sys admins and generally run inside SQL Agent jobs with full system privileges.

I haven't tried implementing it to see what the real pros and cons are but I read you can install the Integration Services (IS) service on a server not hosting an instance of a database engine. If that is practical that would be ideal as I would not bother installing IS on the server hosting the database engine and would therefore never have to worry about local items accessed by an SSIS package or about SSIS packages using memory to the point where it encroaches on the buffer pool.

Thus far however I have always seen SSIS packages be run on the same machine where the database engine resides, and always via SQL Agent jobs. For that scenario all I can do is look to prevent access to local resources. If I have my say all files are accessed via UNC path on a shared file server -and- SSIS packages are run under SQL Agent proxy accounts having the least amount of privileges necessary to do their work (e.g. access to UNC paths, access to database engine only to necessary objects, no access to local drives).

Heh... gosh no. Quite the opposite, actually. A lot of good folks have said they don't use xp_CmdShell because of security implications but then open doors in all sorts of other places. Sure, a lot of it is for the DBA and his/her tasks but I've also seen the same people who prohibit the use of xp_CmdShell give extraordinary privs to production databases and servers. It just strikes me odd that anyone would do that.

Understood on both those points. I'm definitely not a Security Ninja, myself. That's part of why I've been asking questions. And, no, I'm not trying to push the use of xp_CmdShell on anyone. Given the statements I made about its secure and proper use, I will admit that I'm more than a bit curious about why people think its such a bad thing. I've come to the conclusion that it's partially because people don't actually know how to use it securely. 🙂 It's definitely NOT as simple as giving an app or use privs to run it directly which I agree IS a major security concern especially since THAT simple method requires that the app or user be given "SA" privs.

I did the same thing but also included SSIS in that thought process. For something that's supposed to be so easy, the folks at work and on hundreds of forum posts sure do seem to have a hard time using SSIS. Of course, I'm on the ignorant side of the house there... I've not trained myself to use SSIS. It's on my list of things to do in the near future so I can make a better determination. Afterall, a lot of people have problems using T-SQL and that hasn't detered me from using it. :hehe:

Our application developers have been tasked, not with file system maintenance tasks as a DBA might do, but with some pretty heavy ETL tasks (both importing and exporting). I don't know the details of it all but they claim that it's been more than painful especially since the jobs that need to be accomplished are on an ad-hoc "Ok, file #x is ready to load" type basis. So I've been looking for alternatives and xp_CmdShell is one of those alternatives. At this point, I can control the security and allow low-priv developers, users, and applications to get done what they need to in a very simple manner... run this proc with those parameters.

Good info. Thanks. I'll have to take a look at that possibility.

I'll second that. Unfortunately, they haven't done that where I work. SSIS has the proverbial keys to the city and I'm working on getting that changed.

Figured I'd throw my hat in the ring here in regards to the SSIS components.

You can and that's really only useful in a clustered environment where you're usually offloading not the IS work, but the AS work, particularly during overnight loads and cube builds. You still need to license the machine as though you placed the engine on there. It's nice, its useful, but practical... well, see below.

It's actually not practical, because of the licensing component. However, what we're currently doing is using SSIS via a dedicated agent located on the IS servers (we do HUGE bulk loads overnight, a dedicated machine is nice), use the local engine for scratch tables if necessary, and the only thing the foreign servers need to do is read/write. We've found it quite effective, even if I do choke the memory on occassion when I code badly. :blush:

The other part of that is, and here's where the security practice comes in, that the SQL Agent for the SSIS jobs has VERY different allowances than the agents on the main servers. Our main server's agents are 'keys to the kingdom' type logins, at least on their local machines, so the DBA's can do any kind of maintenance they need to. Our SQLAgent, however, can't do crapola unless it's specifically allowed anywhere. Which means when we code up a new ssis package, it needs to get doors opened, etc etc.

I disagree that SSIS is the equivalent of xp_cmdshell, as you can heavily restrict what the package, or the agent, can do. SSIS control relies entirely on the SQLAgent (or developer rights). Restrict the SQLAgent login, and it has as much power as you let it.

Okay, now I'm curious, because I can't see any correlation between SSIS being used, and xp_CmdShell being enabled.

An SSIS package, even if run under sysadmin rights can still only do the things it's built to do. If all it's got in it is a dataflow that moves rows out of text files into tables, it doesn't somehow magically gain the ability to sabotauge your server if it's run under the wrong privileges. All it can do is move data.

How does that compare with xp_CmdShell being run under elevated rights? It's not the same ballpark. It's not the same league. It's not even the same sport.

I am sure I am not alone in thinking that if you were to decide SSIS was worth your time you would get the most from it and be teaching people how best to use it very short order.

Thanks for the info. I could see that being the #1 reason why it's not the prevalent IS system architecture.

This is how I see it done, and how it's done at the shop I'm currently in, and I agree it works quite well. The local engine acts as an "ETL hub" if you will, trafficking data into, out of and within the environment however it does not accept any end-user connections, i.e. SSIS packages and DBA scripts are the only things creating workload.

xp_cmdshell can be used by everyone on the instance but that means everyone will assume the identity of the xp_cmdshell proxy account credentials and you only get one of those per instance. For this reason I think we have more granular control with SQL Agent proxy accounts, of which you can create as many as you need and each job step can use a different one. You can restrict direct developer access to xp_cmdshell and only allow its use through stored procs created by someone else (which is what I'm hearing that you have done Jeff) but at that point it no longer qualifies as a developer tool in my book and it's simply a means to an end. That said, it creates a dependency that I would rather not have in place.

I think in Jeff's instance it's more of a developer functionality issue. The locking down of the feature seems to be secondary to the concern about how to get the job done. And yes, when you say "can xp_cmdshell do the job?" the answer is yes. So the next concern naturally is "can we implement xp_cmdshell in a secure way?", the answer is also yes. However, in my book, the point was initially missed, which is that the job could have been done in SSIS without ever breaking that programming domain.

Following this train of thought, back to what you said earlier Jeff:

Having an SSIS package "watch" a directory awaiting the arrival of a file is a trivial task and IMHO should not be a reason to implement xp_cmdshell. For my money Jeff, I would press the SSIS team to explain why it is that all the work cannot be done in SSIS, and to leave xp_cmdshell out of the equation.

Here is a link to the SSIS add-on I alluded to. It exposes the functionality of a .NET class, System.IO.FileSystemWatcher, as an SSIS task:

SSIS File Watcher Task

Agreed and there's some confusion in what I stated. It's not a matter of folks running SSIS jobs that's the problem at my place of work... it's the privs they've been given and they can create their own packages... in production. It's a separate problem but it's similar to the xp_CmdShell problem in only one way... people who give privs to developers to run xp_CmdShell directly in production have made the same mistake as giving them such elevated privs no matter what the program may be, including SSIS.

That's one of the points I tried to make in my very first post on this thread. There are ways to give developers or users or low priv apps the ability to execute procs that contain calls to xp_CmdShell without them being able to execute xp_CmdShell directly. The bottom line is that someone with only "PUBLIC" role membership can run a proc that contains xp_CmdShell, but they can't run it directly themselves nor can they build such a proc themselves. It takes a DBA (or someone with "SA" privs) to promote such procs to production so they'll actually work.

Using the methods that most people use to deploy xp_CmdShell, that would be correct. But doing it the right way (2 right ways, actually), that problem goes away.

I didn't want to quote the whole post so I quoted only what would be easily recognizable. 🙂

First, thank you for what I believe is an awesome compliment. I'm seriously humbled by it and can only hope that I would live up to such an expectation. :blush:

Heh... it's a trvial point, for sure, but as you pointed out in another post, even SSIS can't do things like move files older than 60 days to an archive folder. For that, you need a Powershell script or somesuch. Would it be so wrong to run the Powershell script from a secure call to CMD by a stored proc?

Shifting gears, the other thing that I wanted to point out about that particular post and the posts of many others on this thread is that the discussions have been absolutely superb and have far exceeded my expectations both in general tone and the amount of information offered. Hell, I think all of you may have even conviced me to spend some time learning more about SSIS. From what everyone is saying, it might even be fun. 🙂

No, it most certainly would not be wrong to call a PowerShell script from within T-SQL using xp_CmdShell...and it's late so that's all I'll say about that 🙂

I must comment on one small point however since you're thinking about looking into SSIS 😉 While PowerShell is not natively supported within the SSIS programming domain, .NET is, in the form of the Script Task (Control Flow) and Script Component (Data Flow). Think of Script Tasks and Script Components as the "glue" in SSIS. Anything not provided by one of the native SSIS tasks or components can be implemented in .NET (VB.net in SSIS 2005, C# or VB.net in 2008) to run inline with the SSIS package. Inline meaning it can participate in the data pipeline as well as interact with SSIS variables and such. While there is no native "PowerShell Script Task" one was developed that offers similar functionality to what I just described as being offered by the .NET Script Task: http://www.youdidwhatwithtsql.com/powershell-script-task-for-ssis/488/[/url]

That's one thing that makes SSIS so neat, is its flexibility and openness as an application development platform, plus the fact that it is a managed environment like .NET and PowerShell (is my KoolAid mustache showing?). Anyway, I recommended PowerShell in that thread because it was a one-liner and I have PowerShell on the brain right now, however that task could have been done in 10 or fewer lines of C# code in a Script Task. There are probably 1,000 or more examples of the requested C# script on the internet and a post about PowerShell seemed more useful than another lmgtfy.com post...of which BTW I may never do again because my judgement on when to do those sucks 😀

Not to lay it on too thick, and who am I, but IMO it is warranted. I have the utmost respect for you and your work and I am happy we can all agree and disagree in the midst of a spirited, enlightening and civil debate about such topics.

A recap for all from post #1: 😉

So to the rest...

*opens, shuts mouth, decides to think a moment*

But it... no, wait, that's not true. Sounded good though.

What about... no, that's not a good argument either. Hrm.

*does some googling* No... no... no... hm, they fixed that? interesting... no... no... Dangit I know what I'm looking for is somewhere on the intertubes... no... no... no...

... hm, that's not valid either.

..................

I concede that point reluctantly. I know I'm personally horrified at the idea of having my devs write up their own xp_cmdshell with EXECUTE AS permissions as a proc for usage later by a lower-allowance login, and then having to fine toothed comb anything they write, because once you've opened the door who knows what genius is going to copy/paste you into a nightmare and neglect to mention it. If I've got more then 7 or 8 development teams I'm certainly not going to be writing things up for them.

I personally feel more protected by shutting it down completely and making sure that their packages simply CAN'T run anything I don't want them to because the login they're using is restricted.

It's laziness and fear, primarily. It should be called xp_PandorasBox.

Edit: I knew I forgot to include some piece...

I may need to, however, learn more about this before I go any further in this debate:

I have some reading to do tomorrow.

I can see why xp_cmdshell is useful for a DBA and some maintenance task.

But i don't see why any dev would be allowed to use it?

Do you have an example of why it's used?

Exactly.

Edit: Okay, now to clarify.

The fact that xp_CmdShell has been enabled means that any attack vector that bypasses your stored procedure under an elevated rights situation has essentially been granted full access to anything on at least your database server, possibly anything in your domain.

This includes arbitrarily executing binaries in any directory, which means the potential for software installation, including but not limited to viruses, worms, rootkits, et al.

What you're doing by enabling xp_cmdshell at all, is assuming that other security factors will prevent execution of arbitrary T-SQL commands under elevated privileges. That means you are completely confident that no injection attack will make it into the engine under any circumstances, as just one example. And not just confidence now, but confidence for all future situations as well.

In the right environment, I can see having that confidence. However, that would be a very limited circumstance, quite rare.

How, for example, do you achieve that level of confidence towards new hires in your development team? Can you assume that Joe, who was hired last week, knows enough to not accidentally allow an injection attack into your system? Will you still be confident that the junior DBA hired next year to cover for you while you're on vacation will be able to tell the difference in what gets promoted to production? Because turning on xp_CmdShell today means it will still be turned on next year and the year after that.

That's part of the calculation on any security issue, but xp_CmdShell, since it allows arbitrary execution of binaries in the O/S, is a weaker link than most other vectors.