It’s happened again, xp_cmdshell being enabled is flagged as a security vulnerability. The recommendation is to turn it off. I disagree, and here’s why…

First of all let’s be open about the fact that I have a stake in this game. Our monitoring toolset uses xp_cmdshell for a few things. It shells out from SQL and checks the block sizes on the disks, whether power saving is enabled on the server and so forth. It’s extremely useful functionality which would be a pain to replace. But I do find I have to keep answering this question, and often asked by people who should know better.

Let’s also be clear on a few important points:

1. XP_CMDSHELL configuration is off by default.
2. It can be turned on and off by a SQL sysadmin
3. By default, if turned on it can ONLY be used by a SQL sysadmin.
4. If you want a non-sysadmin to use it you can enable that right. You then need to set up a proxy windows account for it to execute under.
5. If no proxy is in place a non-sysadmin can’t use it, and a sysadmin using it will generate a windows process in the context of the SQL Service account.

If you put all that together you will see that only a sysadmin can use xp_cmdshell unless you explicitly configure access for another user, and even more importantly you can’t stop a sysadmin using it.

Therefore whether the setting is enabled or not is irrelevant. The only person who can use it has the ability to turn it on anyway, and you can control the level of access to the operating system by correctly assigning least privilege to the SQL Service account or the proxy account.

Now…the question might rise about whether the functionality itself is a security vulnerability and I’m not going to argue that it’s not a possible attack vector. It provides a powerful integration between the SQL Server and the Operating system. But so does executing an agent job in CmdExec mode and I’ve never seen that flagged in a security audit.

Neither xp_cmdshell or cmdexec agent job functionality is something you can turn off in such a fashion a sysadmin can not turn it back on(although even typing that about 5 things to give a try occur to me) so I think the very best this ‘audit recommendation’ will ever achieve is protection against non-malicious ‘accidents’ by tired DBA’s. Disabling it will be no more than a minor inconvenience for a malicious user who has the rights to use it once enabled.