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.

Option Name:  allow updates

Usage:  sp_configure ‘allow updates’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  Once upon a time if you wanted to update system tables in your database you could do so, and this was the magic switch you needed to throw to do it.  In the current enlightened times people realize that updating system tables is generally speaking not a good idea, and if you really need to do it to (for example) fix an allocation error corruption, there is now emergency mode repair which can be used.

When should I use it:  Never after SQL 2000, and even in SQL 2000 only if you are very clear on what you are doing.  Books Online is pretty clear on this.  If you’ve updated system tables then your database is not in a supported state.  This feature currently has no effect and will be removed in a future version of SQL server.

What else should you know:  There are other ways to update system tables if there is corruption within your database.  But you need to be aware that if you change an underlying system table the date of the last change is logged in the header of the database.  It will be there for everyone who cares to look(ie Microsoft Support) to see.  I’m not going to go into details on that accept to point you over to Paul Randal’s blogs on database corruption.  Though if you are looking at this stuff god knows how you would have found my blog before his.

Option Name:  Show Advanced Options

Usage:  sp_configure ‘show advanced options’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  If you have just installed SQL Server and run sp_configure you are going to get 18 options.  If you enable(Set to 1) the ‘show advanced options’ option you get access to all 70 options.  What’s an advanced option?  To be honest the list looks pretty arbitrary to me.  Things like ‘backup compression default’ and ‘remote admin connection’ are fine as ‘simple’ options, but why would ‘clr enabled’ or ‘nested triggers be considered simple?  You can do some serious damage with just the simple options.

The only official word I can find on it describes the advanced options as ‘Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician’.  There’s a few advanced that I feel could probably be simple, and a few simple that could probably be advanced.  As we progress through the month I’ll talk about each of these and the damage(or not) they could do.