• Lynn Pettis (9/19/2012)


    opc.three (9/19/2012)


    Lynn Pettis (9/19/2012)


    opc.three (9/4/2012)


    Jeff Moden (9/4/2012)


    opc.three (9/4/2012)


    All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.

    Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉

    True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).

    Locked doors only keep honest people out.

    If you have the privledges and the will to do something you shouldn't there isn't much to stop you.

    Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.

    At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.

    Actually, it is applicable. I do secure the sa password. In fact, at my last full-time employer using SQL Server, I was the only one that knew the sa password on our DW and PeopleSoft servers (sorry, but I didn't have much control over the SIS system and it used sa for the application, which I hated). The password was written down, in a sealed envelop, locked in a secure location that only two people knew, and my boss wasn't one of them.

    It is always applicable in a general sense, but it's not something any reasonable person would relay to explain why a security breach or data loss occurred.

    Manager: What happened?

    DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.

    Manager: How did they get in?

    DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.

    Manager: Who know the sa password?

    DBA: Everyone, it's written on the conference room's whiteboard.

    Manager: What? Why?

    DBA: I am not sure what you mean. Locked doors only keep honest people out.

    All I am saying; if there is a will, and someone has the permissions, all the roadblocks you put up are only going to slow someone down, not stop them. At some point, you have to have some trust in the admins of your systems, but that doesn't mean you don't audit what they do.

    The 'slow vs. stop' argument you make is a fair and that is precisely the game in which we should be compelled to participate. Just because we cannot stop everyone all the time doesn't mean we shouldn't be diligent about trying to stop the majority of the people all the time, stopping the rest of the people some of the time, and slowing down the remainder. Trusted servants should be kept to a minimum and their actions should be auditable, and that auditing should be layered and should extend to any action in all domains (including the OS when initiated from the database).

    The above conversation is an absurd example but it illustrates how the sentiment can differ from the implementation. This is a healthy debate to have. It's up to us where to draw the line and mine just happens to be well before enabling xp_cmdshell. The security exposures xp_cmdshell brings with it outweigh whatever flexibility it may add when using T-SQL, and even that flexibility is debatable when compared with tools like SSIS and PowerShell.

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