SQL Server Blog

The Real Danger Regarding the xp_cmdshell Setting

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

When considering database security, you might find stories of data breaches involving SQL Server with reports like this.

“The researchers note that the ransomware infection starts with the MS-SQL process on the compromised machine downloading a .NET file using cmd.exe and powershell.exe.”

That most likely means the hacker in the incident gained access to SQL Server, and then used xp_cmdshell to open a Windows command shell. With that shell opened, they could then pass DOS and/or PowerShell commands to collect information and download malware onto the server.

It sounds dangerous. It is. But whether or not xp_cmdshell is enabled is irrelevant.

I’ve been fortunate to work in several different industries throughout my career, and several of them had compliance requirements that called for disabling the xp_cmdshell setting. At a basic level this sounds sensible, but there are two big considerations for using xp_cmdshell.

  1. Only principals with CONTROL SERVER permissions can use xp_cmdshell.
  2. Only principals with CONTROL SERVER permissions can enable or disable this setting.

This basically means that anyone with these permissions, which includes members of the sysadmin role, can not only use the setting, but also turn it off or on at their whim. No restart is required, and the change in the setting is instant with a simple execution of RECONFIGURE.

Now, we can take measures to try to limit the usage of xp_cmdshell, but this is almost as futile as disabling the setting. We can set up Policy-Based Management, or even enable triggers to prevent enabling this feature, but those same users with CONTROL SERVER permission can disable any of these features as well.

In my experience, when I see this feature enabled on a SQL Server instance, I don’t reflexively disable it. Rather, I discuss the feature with administrators or developers and ask if they are using xp_cmdshell in any of their code, and if so, what are they using it for. The most common way I’ve seen is to check a directory for files, and then write that information into a table. Depending on usage, that can be a valid solution for a business requirement.

You might be interested to know there are even some system objects from Microsoft that use xp_cmdshell, especially with regards to replication. Depending on what features you have installed, you might see results for this query.

SELECT definition
FROM sys.system_sql_modules
WHERE definition LIKE '%xp_cmdshell%';

If Microsoft is using it, you probably shouldn’t feel too bad about using it either.

As a general rule though, it’s probably a good idea to disable features you don’t use, especially if you have a compliance requirement to do so. But I hope you can see now that disabling this feature doesn’t do much to increase the security of your SQL Server instance. You’re better off spending time trying to identify which principals have CONTROL SERVER permissions, including the members of the sysadmin role, and then verifying they are trusted and they require those permissions.

Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This