xp_cmdshell is a handy function and allows SQL Server database administrators to complete tasks quickly and get transformed as a proactive and efficient database administrator. However, it also allows the user to run windows programs using the SQL Server’s service account. In this article, you will learn how to control the status of xp_cmdshell from your Central Server.
On a single server
To check the status of xp_cmdshell on a single SQL Server instance, you can run this command from a new query window. If the value is 1, xp_cmdshell is enabled on that SQL Server instance. If the value is 0, xp_cmdshell is disabled.
SELECT * FROM SYS.CONFIGURATIONS WHERE Name = 'xp_cmdshell'
If the xp_cmdshell is enabled, disable it using this command.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 0 GO RECONFIGURE GO
Many DBAs might enable xp_cmdshell for automating some tasks and do not turn it off. So, it’s a good practice to track the status of xp_cmdshell and disable it if it was enabled.
Let us automate it
You should be setting this automation from a Central Server. Create linked server for all target servers and then store server names in a table @Tserver. Through cursor the script would fetch each server name and then disable the xp_cmdshell on it. Please note, the script is not checking the status of xp_cmdshell. It directly disables it. If you want to enable the script to check the status of xp_cmdshell and then disable it, then you should use sys.configurations inside the script.
declare @Tserver table (cserver varchar(200)) insert into @Tserver values ('Server1') declare C1 cursor for select * from @Tserver declare @cmd nvarchar(2000), @server varchar(200) open C1 fetch next from C1 into @server while @@FETCH_STATUS = 0 begin set @cmd = N'EXEC (''EXEC sp_configure ''''show advanced options'''', 1 RECONFIGURE EXEC sp_configure ''''xp_cmdshell'''', 0 RECONFIGURE'') at [' + @server + ']' print @cmd exec (@cmd) fetch next from C1 into @server end close C1 deallocate C1
In some servers, it might be necessary that the xp_cmdshell is enabled. In such cases, you can create another reference table, store all server names that require xp_cmdshell in this table. When fetching server names from the first table i.e., @Tserver, you should also check this table and if the server name is in the reference table, then skip it. You can also enable xp_cmdshell for a short period. To do that, create a table and add 2 columns. The first column should be server name and the second column should be the date when xp_cmdshell is disabled. The script should be made to refer this table and if the deadline has been reached, xp_cmdshell should be disabled on the server. Many DBAs recommend to turn off xp_cmdshell . That is as part of security measures. However using xp_cmdshell will never cause SQL Server corruption.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com