• Jeff Moden (9/5/2012)


    opc.three (9/5/2012)


    That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.

    First, done properly, there is no additional risk. The only time such a risk occurs is if an application or non-DBA users are allowed to have "SA" privs. If such a thing happens, then you have [font="Arial Black"]much [/font]more to worry about than the use of xp_CmdShell. Even if you were to rename or even delete the DLL for xp_CmdShell, there are still super easy hacks to get to the command line if you have "SA" privs. In fact, following the rules to correctly instantiate the use of xp_CmdShell (and it's not obvious in Books Online) will make you tighten your security to what it actually needs to be.

    Your assertion about the "non-DBA" may be where we begin to digress. Internal threats are analogous to high blood pressure: they are silent killers. The less exposed a server that holds a business' most valuable commodity can be made to be the better chance the business has to survive a disgruntled, malicious or careless employee. Your argument has not addressed the fundamental fact that having xp_cmdshell enabled increases the known attackable surface area of the database server. All other workarounds and undocumented hacks aside, why expose a convenient way for a user to access a server's file system and possibly network file systems when they may not otherwise have direct access to the server itself?

    Examples of why I'd want to use xp_CmdShell are two fold.

    The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.

    This sounds like a bit of a pre-judgement. Why not?

    Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities. I am sure selling another Windows license is not a bad side-effect for Microsoft, but it makes independent actors in the environment easier to manage, tune, secure and recover. It sounds like you have your ETL staging databases, where the processing of large data files into staging tables and further transformations of that data are taking place, sitting on the same instance as application-facing OLTP databases. How do you distribute your workload across multiple servers with everything in T-SQL sitting on one server? How do you tune so memory required for ETL does not flush buffer pool memory needed for OLTP databases?

    The second is security. It's a lot easier to keep one system secure than it is two. To use your own words, it cuts the "additional risk" in half simply because there's one server instead of two.

    Well if I may, to paraphrase your words: in a "properly locked down system" the risk is actually lower with two servers than with one since no application code will ever access the file system of the SQL Server.

    I won't get into intangibles such as not having to hire programmers that know a certain language to deal with all the scripts that developers tend to write in SSIS because they may not know how to do something in SSIS (or that SSIS simply can't do it) or the fact that those scripts are frequently written in T-SQL anyway.

    All I can say is that SSIS has been out for 7 years and PowerShell for 6. You might be the exception but from hanging out on these and other forums I see that at one time or another most DBAs and DB developers have run into a problem where one of those tools (and I'll even include VB Script as a legacy throw-in even though I would not recommend it for new development) appeared to be better suited to solve the issue than anything that could be done in T-SQL, even when considering the augmentation of the language with Windows Batch.

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