Home Forums SQL Server 2008 SQL Server 2008 Administration If you could use xp_CmdShell securely, would you? RE: If you could use xp_CmdShell securely, would you?

  • August 2, 2015 at 10:51 am

    #1816680

    Michael L John (8/1/2015)

    Jeff Moden (8/17/2011)

    Jayanth_Kurup (8/17/2011)

    Probably not in production. While there are secure ways of using xp_cmdshell. I can't/won't trust everybody to use it the way its meant to be. I could leave the company in a few years and don't really know how others would use it and I wouldn't want to leave a bad legacy.

    I have a huge appreciation for that, Jayanth... not trusting others to do things correctly either because of perceived inconvenience on their part or a simple lack of knowledge is a problem for many DBA's including myself. It's a bit of paranoia that good DBA's not only agree with, but strongly embrace, as well.

    Let me change the question a bit to match this particular problem. What privs do GUI login(s) currently enjoy against your production systems? How about individual non-DBA users (including but certainly not limited to Developers)? Do they have at least "DataReader/DataWriter" privs instead of only the privs to EXECUTE "parameterized" stored procedures? If so and someone deletes or overwrites a bunch of data, would that be considered to "leave a bad legacy", as well?

    Actually I have it enabled in production, and disabled in all other environments.

    Why? In the other environments, its more of "keeping an honest person honest". There are some seriously silly things I have seen done through xp_cmdshell by DBA's (a loosely used term in this case!) as well as developers.

    Example? The app would take a request for service and automatically assign it to the closest technician. When the technician was assigned, a proc called an executable using xp_cmdshell that connected to an SMTP server. The executable created a file on the file share that was a copy of the email. Then, inside a loop(!), the proc kept calling DIR "filename" until the file appeared. Once it found the file, it finally moved on.

    The SMTP server was across the country in a different data center. The net effect was that the system appeared to hang until it found the file.

    So, in production we use it for various maintenance tasks. Everywhere else,. nobody knows it exists!

    That's a pretty good example of why xp_CmdShell get's such a bad name. I've used it to build complete ETL solutions that worked really well (including logging into and doing a download from an FTP/SFTP sites) but all of the procs were "self-healing" and didn't do stooooppppiiiidddd things like waiting indefinitely (or even a little) for something to happen.

    Of course, people do the same stooooppppiiiidddd things with front-end code, managed code, PoSh, Active-X, Perl, C#, VB, and what-have-you but you never hear about that stuff. That's part of the reason for the "play on words" quote in my signature line below.

    Switching gears, I submitted two sessions for the upcoming SQLSaturday in Pittsburgh. I really enjoyed the folks out there and hope I get selected. Oddly enough, one of the sessions is the one I attached previously. The other is on a method for automatically importing and mapping some fairly complex spreadsheets automatically without having to do remaps every month when they add new sections for the most recent month passed.

    --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.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)