Common SQL Server “wisdom” says that xp_cmdshell is a security hole.

It is not. The true danger to any shop is unchecked SQL administrator rights…whether or not xp_cmdshell is enabled. (To audit SQL security quickly, get a Minion Enterprise trial. The trial alone will save you days, if not weeks, of work.)

The true danger to any shop is unchecked SQL administrator rights…whether or not xp_cmdshell is enabled.

– Me, just now.

That common “wisdom” about cmdshell has seeped into the minds of anyone who has ever heard of Microsoft SQL Server. Technologists and management the world over are convinced that xp_cmdshell will destroy their company. Let’s walk through some of the fears surrounding xp_cmdshell.

In this article:

• xp_cmdshell: fear vs. reality
  • xp_cmdshell is powerful
  • xp_cmdshell was dangerous (in the 1990s)
  • Present Day: xp_cmdshell is secure by default
• Audit SQL administrator permissions!

xp_cmdshell: fear vs. reality

Fear: “If we enable xp_cmdshell…”	Reality
“…anyone can run Windows commands!”	By default, the right to execute xp_cmdshell is limited to SQL Server administrators. Any non-administrator needs specifically granted CONTROL SERVER permissions, if they want to execute xp_cmdshell.
“…we’ll be going against Microsoft’s best practices!”	Microsoft itself uses xp_cmdshell. (See the system_sql_modules query here.) They do recommend that you leave xp_cmshell disabled if there’s a way around it. (See docs.Microsoft.com.) On occasion, there is no way around it.
“…it’s still a security hole!”	No, it’s super not. Here’s why: Xp_cmdshell is disabled by default. When it is enabled, only administrators – the group trusted with all-powerful SQL permissions, the people who can enable xp_cmdshell any time they want to anyway – can run it. If you’re a non-administrator, and if you have not been explicitly granted CONTROL SERVER, you simply cannot use xp_cmdshell.

Again: the real danger to SQL environments is unchecked administrator rights. If someone has admin rights, they can cause all kinds of mayhem, with or without xp_cmdshell.

So then, why does (most of) the entire SQL Server-using world think that xp_cmdshell is dangerous?  Two reasons: power and history.

xp_cmdshell is powerful

Cmdshell is a very powerful feature.  A login who has permissions to run it could indeed do a lot of damage.

Of course, logins that have this permission have administrator rights. By definition, admins can:

• export and delete data,
• drop databases and objects,
• run SHUTDOWN, and
• perform all kinds of mayhem.

The many “dangerous” features in SQL Server – including xp_cmdshell – are all kept in check by controlling permissions to them. SQL won’t suddenly forget how to deny permissions, just because it has a fancy “xp” in the name. SQL Server honors all security, and xp_cmdshell cannot override it.

This is exactly why we only want trusted team members to have sysadmin rights. The “Audit permissions!” section below outlines some actions to take. Consider it your call to action.

xp_cmdshell was dangerous (in the 1990s)

Cmdshell has been around forever. Way way back in the day, when you installed SQL Server, xp_cmdshell was wide open to the public by default! This is where the problem started.

In those olden times, SQL Servers graphical interface allowed way too many people – often, people who had no idea what they were doing – to create and manage databases. “Ease of use” really helped SQL Server succeed and take hold in the industry.

But “easy to use” means that lots of untrained DBAs had no idea that xp_cmdshell was even there.  By default, any given SQL Server instance was completely vulnerable and they didn’t even know it.

The 20th Century was a more innocent (or if you like, a really dumb) time for security. It’s true that Microsoft should never have packaged up something so powerful, with the default setting of “yes of course anyone can use this!”  Of course, this was also the era of installing the SA account with a NULL password by default. And it wasn’t just Microsoft: Oracle had their scott\tiger username and password combo.

We’ve all grown up a little bit in the last few decades.

Present Day: xp_cmdshell is secure by default

For many, many years now, xp_cmdshell is disabled by default. Every new installation of SQL Server has a big 0 for ‘xp_cmdshell’.  When you enable cmdshell, it’s not available to public, it’s not available to the non-administrator user.

Again: If you have a handle on your SQL Server administrator privileges, you don’t have to worry about enabling xp_cmdshell when it’s needed. If you don’t have a handle on those privileges, you have much bigger problems.

Even if you disable xp_cmdshell, anyone with admin rights can turn it right back on. The moral is: Tend to your admin rights!

Audit SQL administrator permissions!

The Center for Internet Security (CIS) SQL Server 2016 Benchmark audit says:

The xp_cmdshell option controls whether the xp_cmdshell extended stored procedure can be used by an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client.

Rationale: The xp_cmdshell procedure is commonly used by attackers to read or write data to/from the underlying Operating System of a database server.

– http://www.itsecure.hu/library/image/CIS_Microsoft_SQL_Server_2016_Benchmark_v1.0.0.pdf

This is correct only if we’re talking about an “authenticated SQL Server user” with administrator privileges. We’ve already seen that the problem is not xp_cmdshell; the real problem is admin privileges that aren’t locked down.

Whether or not you enable xp_cmdshell, you must lock down security. A few of the security basics are:

1. Audit who has administrator rights on all of your SQL Server instances. This will require you to dig into Active Directory groups and subgroups to see who inherits permissions from a parent group.
2. Enforce password policy for all logins.
3. Limit who has the SA password. Preferably, limit this to “nobody but the DBAs”.
4. Check for existing passwords that are too simple.
5. If possible, use Windows authentication only. This isn’t always possible, but it’s nice when you can get it.

Best practice: Automate security auditing

Speaking from experience, auditing security is a time-consuming, tedious, and error-prone process. That’s one of the major reasons we created Minion Enterprise. ME does all the “grunt work”:

• gathering login and permission information,
• unraveling Active Directory subgroups,
• alerting on logins with newly acquired admin permissions, and
• alerting on weak passwords.

The DBA can then review the results and take action as needed.

If everyone and their dog has administrator rights – either through their own account or via the SA password – you have much, much bigger problems than xp_cmdshell.

Get a Minion Enterprise trial today, and run your initial security audit. It will save you days of work.