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 1, 2015 at 7:38 pm

    #1816633

    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!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/