xp_cmdshell in sql server

Hi,

today I got a request to enable xp_cmdshell on my sql server because an application needed the functionality to list the content of a directory out of a t-sql stored procedure.

The functionality is pretty old (already supported by sql server 2000), but evil referring to sql server security blog entry: http://blogs.msdn.com/b/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx

If security context is not set with caution enabling this setting enables a user to access a command line to the server with administrator rights. This is bad. (Default is to access the command line with the security context of the sql server service account, but it can be changed to other credentials using: sp_xp_cmdshell_proxy_account )

Syntax:
xp_cmdshell { 'command_string' } [ , no_output ]

Enabled the feature:

1 2 3 4 5 6 7 8 9 10 11 12

-- 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 -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO

reference: http://msdn.microsoft.com/en-us/library/ms190693.aspx

Check if it is turned on or off:

1 2 3 4 5 6 7

SELECT name AS [Configuration], CONVERT(INT, ISNULL(value, value_in_use)) AS [IsEnabled] FROM master.sys.configurations WHERE name = 'xp_cmdshell'

reference: http://sqltidbits.com/scripts/check-if-xpcmdshell-enabled-across-multiple-servers

... and here an example how it can be used (found in the web):

1 2 3 4 5 6 7 8 9

create table #tmp(result varchar(255)) insert into #tmp exec master.dbo.xp_cmdshell 'ping yahoo.com' if exists(select * from #tmp where result like '%request timed out%') print 'timeout' else print 'reply' drop table #tmp

references: http://www.nullskull.com/q/11008/xpcmdshell-output.aspx

and even better:

1 2 3 4 5 6

declare @results table(result varchar(255)) insert into @results exec sp_executesql N'xp_cmdshell ''ping www.yahoo.com''' select * from @results

reference: http://stackoverflow.com/questions/1842126/call-tracert-and-ping-from-sql-and-put-the-result-in-sql-table

kind regards,
Daniel