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.
- Only principals with CONTROL SERVER permissions can use xp_cmdshell.
- 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.
