SQL SERVER – Is XP_CMDSHELL Enabled on the Server?

I am not a big fan of using command line utilities in general. But from time to time I do explore and play around with command line tools that make my life easier. Having said that, when working with SQL Server, I do often give out the recommendation of not trying to use the native xp_cmdshell commands with SQL Server. Even the SQL_Server_2012_Security_Best_Practice_Whitepaper talks about xp_cmdshell and recommends to avoid the same if it is possible. These are basic building blocks of security that I highly recommend my customers from time to time.

In this blog, let me show you two ways of doing the same.

TSQL Way

The easier way using standard T-SQL commands is shown below.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- Disable xp_cmdshell option now
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

This works great when you have direct access to the SQL Server box and you want to just do it on the box under question.

PowerShell Way

For the world of administrators who manager literally 10’s or 100’s of servers in a datacenter, doing a line-by-line or a server-by-server T-SQL script doesn’t work. They want something as a script. Below is a PowerShell script that will find out if the xp_cmdshell option is enabled on a server or not.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null;
 $srv = new-object Microsoft.SqlServer.Management.Smo.Server("MSSQLSERVER")
 if ($srv.Configuration.XPCmdShellEnabled -eq $TRUE)
 {
 Write-Host "xp_cmdshell is enabled in instance" $srv.Name
 }
 else
 {
 Write-Host "xp_cmdshell is Disabled in instance" $srv.Name
 }

I am sure this can be run in a loop for all your servers. Here the default instance MSSQLSERVER is being used. I am sure you will be able to run the same script in PowerShell ISE to know your instance status wrt xp_cmdshell. I would highly recommend you to share your experience and how you ever used such scripts in your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)