Ensure 'xp_cmdshell' Server Configuration Option is set to '0'

Question

Hi,

We are using SQL Server 2017 in our production environment. We got following requirement from our security team.

Ensure 'xp_cmdshell' Server Configuration Option is set to '0'

After checking it was set to 1 from previously. Is there any impact if we set it to 0? what 'xp_cmdshell' is used for?

Answer 1

xp_cmdshell is used for to running DOS-level commands from SQL Server. For instance:

EXEC xp_cmdshell 'DIR C:\'

Whether there will be any impact depends on the applications running on the instance. It is quite popular among developers to export data from SQL Server by spawning BCP through xp_cmdshell.

You can run this query in all user databases to see if there are any stored procedures or similar to see if xp_cmdshell is being used:

SELECT s.name + '.' + o.name
FROM   sys.sql_modules sm
JOIN    sys.objects o ON sm.object_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
WHERE sm.definition LIKE '%xp_cmdshell%'

Note that this query only gives an indication. The code you find may be dead, and thus there would be an issue to disable xp_cmdshell. On the other hand, there could be client that submits batches with xp_cmdshell in. Ultimately, you would need to talk with the application teams.

Answer 2

Hi @Kazi Ariful Haq ,

Although @Erland Sommarskog 's reply is excellent, I also want to accentuate the importance of this feature and its why it is important.

Is there any impact if we set it to 0?

Yes. There is.

what 'xp_cmdshell' is used for?

This option allows system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations.

And quote from this doc.: securing-sql-server-surface-area

With the “xp_cmdshell” server configuration option, you can control whether members of the SysAdmin fixed server role can execute the extended stored procedure xp_cmdshell and thus interact with the Operating System onto which SQL Server instance is installed.

If you enable this feature, along with the functionality that is offered, you also create the risk of having someone that maliciously gained SysAdmin access on your SQL Server instance, to also gain access to the Operating System of the database server and thus be able to execute OS commands. So, unless it is required and allowed by your Organization’s security policies, you should not enable this feature.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Kazi Ariful Haq ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.