Today we’re going to talk about “locking down” xp_cmdshell.
Quite a lot of data folks are reasonably concerned with the possible security holes that xp_cmdshell could introduce. Unfortunately, all the wrong things get all the attention.
In this article, we cover what you really need to understand and secure xp_cmdshell: foundations, the “sysadmins only” club, service accounts, and MSDB rights and proxies. And of course, there’s a nice “bottom line” summary at the end.
(A note on terms: I will often refer to xp_cmdshell by the nickname “cmdshell”. It’s simpler to type and to say.)
1. Foundations of xp_cmdshell
First, a definition: xp_cmdshell is a SQL Server system stored procedure that “spawns a Windows command shell and passes in a string for execution.” In other words, it lets you run Windows commands from within SQL Server.
Xp_cmdshell is disabled by default on a fresh installation of SQL Server. Check the status of cmdshell using sp_configure:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure; /* Scroll down to xp_cmdshell and check the run_value */
EXEC sp_configure 'show advanced options', 0; /* Let's hide those advanced options again */
RECONFIGURE;IF cmdshell is enabled – that is, if run_value = 1 – you could run something like this within SQL Server:
EXEC xp_cmdshell N'DIR c:\temp\';Cmdshell passes the command “DIR c:\temp\” to Windows, which just retrieves a directory listing for the “temp” folder on the C: drive.
The incredible power of xp_cmdshell
What’s more, you could potentially run commands to:
- create, read, modify, or delete files;
- create users and add them to the Local Administrators group; or
- any of a vast number of other scary things.
(Why potentially? See sections 2 through 5 for that answer.)
No wonder people are so afraid of cmdshell, right? In fact, folks are so worried that the vast majority of SQL DBAs – and many SQL audit specifications – require xp_cmdshell to be disabled, period. It’s really too bad that this does us almost nothing for security.
Summary: xp_cmdshell lets you run Windows commands from SQL Server. This causes many people a lot of concern.
2. The cmdshell club is “sysadmins only”…mostly
The first and most critical security tip you need is this:
By default, only members of the sysadmin SQL role can run xp_cmdshell.
Two very, very important items follow from this fact.
First, you need regular sysadmin audits
It is absolutely vital that every single SQL Server shop audit sysadmin role membership on a regular basis. Security audits are critical even if we’re not talking about cmdshell, because you don’t want Bill from accounting or Claudia-Jean the CIO accidentally “making a little change in production”, like “dropping our customer and inventory databases”.
What’s more, it’s always wise to keep the number of sysadmin accounts to a minimum. If you have dozens of sysadmins, you have dozens of opportunities to get hacked or spoofed!
Second, you need to know the loophole
Every DBA and audit that feels safe by simply disabling xp_cmdshell is committing security theater. To illustrate why, let’s look at an example:
- Jamie is a DBA with sysadmin rights across the entire environment. Whenever they need to, Jamie first enables xp_cmdshell, then runs the command(s), and finally disables cmdshell again. No problems.
- Alex is a user with read-only rights to the databases on Server1. To be clear, they do NOT have sysadmin membership. If Alex attempts to run xp_cmdshell while it is enabled, they get an error.
Jamie can run xp_cmdshell at any time, and Alex can’t. What exactly do we secure, by requiring xp_cmdshell to be disabled? (The real answer is: “Possibly a little bit. But probably nothing.” Read sections 3 and 4 for more.)
Summary: By default, only members of the sysadmin role can run xp_cmdshell. Sysadmins can also enable cmdshell, so requiring it to be disabled is, frankly, rather silly.
3. SQL service accounts are a very big deal
Here’s another big, key fact to note: When a sysadmin runs xp_cmdshell, the request to Windows runs under the SQL Server service account.
“The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.“
– Docs.Microsoft.com
This is critical to understand, so let’s walk through an example:
- A server named Server1 has a default instance of SQL Server.
- The SQL Server instance’s service account is a local Windows account, named “Server1\mssql_acct“.
- I’m a DBA, and my login – “Domain\Jen” – is a member of the sysadmin role on the Server1 SQL instance.
- Today, I connected to SQL on Server1 (using my “Domain\Jen” login) and made sure that xp_cmdshell is enabled.
- Then, I run a cmdshell statement: EXEC xp_cmdshell N’dir c:\folder2\’;
- SQL Server passes that “dir” request over to Windows on my behalf. Instead of using my credentials, SQL on Server1 will always present the request using the “Server1\mssql_acct” credentials (the SQL Server service account credentials).
The SQL service account – it’s a trap!
SQL Server shops commonly use a local administrator (or worse, domain administrator) as the service account. Even more dangerous is the common practice of sharing service accounts across multiple SQL Server instances. This has disastrous effects on your security:
A person who gets sysadmin rights on one SQL instance…
| where the service account is a member of… | and the service account is… | results in… |
|---|---|---|
| anything with any rights | used across many servers | some level of rights on many servers. |
| the local administrator group | unique to that server | god-tier access on that server. |
| the local administrator group | used across many servers | god-tier access on many servers. |
| the domain administrator group | either unique or shared | god-tier access on every server in the entire domain. |
The solution, of course, is to not do these things! Let’s reword that into action items:
- Make sure each SQL service account is unique per instance. (Yes, it’s a pain…but nobody said security is easy!)
- Make sure SQL service accounts are not a member of the local administrators group.
- Grant the service accounts permissions explicitly if you need the service account to have rights to do something locally, or on another server.
- Make sure the SQL service accounts are not domain admins.
The SQL Agent service account matters, too. Why should the Agent matter? Let’s talk about that in the next section, “MSDB rights”.
Summary: Only a sysadmin can run xp_cmdshell, but when they do, SQL presents the request to Windows under the SQL Server service account. If the service account
1. is shared across more than one SQL Server instance, and/or
2. is member of the local administrator group, and/or
3. is a domain admin,
that opens quite a few massive security holes.
4. MSDB rights are tricksy
Sysadmins can run xp_cmdshell, and only a very few trusted people (should) have sysadmin rights on any given SQL Server instance. So far, so good!
For this example, you and I are not sysadmins. Now, let’s put on our sneaky-hacker hats and look at the MSDB database and the SQL Agent:
- SQL Agent jobs run under the context of the SQL Agent service account.
- The SQL Agent service account must be a member of the sysadmin role. (Nope, no way around this.)
- Nearly everyone and their dog has rights to the MSDB database.
- Job-related tables in MSDB are user tables, and can be updated directly (INSERT/UPDATE/DELETE). You’d think that system tables in MSDB are really system tables, but nope.
- Therefore – because the SQL Agent service account is indeed a member of sysadmin – anyone with write permissions to MSDB can add or update a job step to run xp_cmdshell, and it will run it. The user themself does not have to be a sysadmin…all they need is write permissions to MSDB.
That’s…not good. We’ll talk about what to do, but first let’s prove that job-related tables in MSDB are user tables:
The MSDB security hole becomes exponentially worse if the SQL Agent service account is shared across multiple instances, and/or is a member of the local administrators group, and/or is a domain admin.
In my opinion, the MSDB rights issue is the biggest security vulnerability from xp_cmdshell, and you cannot fix it by disabling cmdshell.
Summary: If you aren’t strictly limiting and auditing rights to the MSDB database (and limiting and auditing sysadmin membership), your environment is vulnerable. It’s even more vulnerable if the SQL Agent account is shared among SQL instances, is a member of the local Windows administrator group, and/or is a domain admin!
5. If you use an xp_cmdshell proxy account…
By default, a user who is a member of the sysadmin role in SQL Server can run xp_cmdshell. Users who are not sysadmin members cannot, unless you specifically create a cmdshell proxy account.
When [cmdshell] is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
– Docs.Microsoft.com
We use a cmdshell proxy account to grant more people the ability to run xp_cmdshell.
It’s a mistake to believe that a cmdshell proxy account simply “locks down” xp_cmdshell, all on its own. The only sense in which the cmdshell proxy account locks anything down is when a user needs rights to run xp_cmdshell, but that user should not be made a sysadmin. You should also make sure that the credential has the minimum possible rights in Windows…and should absolutely NOT be a member of the local administrators group, nor be a domain admin!
(See: sp_xp_cmdshell_proxy_account)
Summary: By default, the pool of users who can run xp_cmdshell is the membership of the sysadmin role. If you need to expand access to cmdshell, use an xp_cmdshell proxy account, and grant the proxy credential minimum rights in Windows.
6. Bottom line: How to secure xp_cmdshell
When we look to lock down xp_cmdshell, our primary goal must be to prevent SQL from becoming a conduit for mucking up the operating system…and files, and network, and other servers in the domain, etc. We absolutely do not want general SQL users to have the ability to freely move around in Windows.
There are four key areas to securing cmdshell:
- Sysadmin: Audit sysadmin role membership. Do it now, and on a regular basis, to make sure that only the required few folks (DBAs!) have those rights.
1a. This includes regularly auditing any Active Directory groups or logins that have sysadmin, too!!
- Service Accounts: Make sure SQL Server and SQL Agent service accounts are NOT domain admin, and preferably not local admin. The SQL Agent service account should have strictly limited Windows permissions.
- MSDB Rights: Audit MSDB rights, now and on a regular basis, to make sure regular users don’t have the ability to create or modify SQL Agent jobs.
- Minimum rights for the cmdshell proxy (if any): If you need to open xp_cmdshell up to non-sysadmin users, set up a proxy with the minimum possible rights to achieve this.
LinkedIn Live event January 20 –
“Learn SQL Server Security: xp_cmdshell edition!”
