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)
6 Comments. Leave new
Thanks for the article Pinal, I remember this command. I have also seen an article how xp_cmdshell can be used to hack the server.
in my scenario we switched to SSIS to do the work what xmdshell was doing.
Immu. Thanks for sharing.
Wny not use SQL Server central management groups for that?
If you have added the instances to the Central Management Server, then you don’t have to worry about instance names or even writing a loop in Powershell.
Łukasz – Yeah. that’s another way of achieving that. Thanks for reminding and giving idea about another blog :)
How can I find out if its being used before I disable it?
keep running profiler or XEvents for few days with filter of xp_cmdshell.