7

Possible Duplicate:
Enable ‘xp_cmdshell’ SQL Server

When I run xp_cmdshell command in SQL Server 2012, I get the following message:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

But, in SQL Server 2000 this query is executed successfully.

Community
  • 1
  • 1

2 Answers2

20

This has been disabled out of the box starting with SQL Server 2005, when they introduced the Surface Area Configuration Tool, in an effort to make SQL Server more secure by default. That tool has since been retired, but you can still control the behavior using sp_configure. An example is shown on MSDN:

-- 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

(I also blogged about this many years ago.)

The reason is that this is a potential security hole. If you allow SQL Server to execute xp_cmdshell, then they can theoretically send any operating system command there, bypassing any and all security you thought you had. This is especially problematic when the SQL Server service account and/or the proxy account have been elevated to sysadmin or other levels because that's easier than explicitly defining only the exact things they should be able to do.

Rather than enable it and disable it to support command-line interaction, a popular way to expose operating system functionality while still having some control over security is to implement the OS-level functionality you need using SQL-CLR. Here is a good starting point for accessing the file system with CLR (however if you search around you will find much more modern and exhaustive approaches).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3

This is disable for sql server 2012. But you can run the following command in sql server 2008..

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
Hasib Tarafder
  • 5,773
  • 3
  • 30
  • 44
  • Exactly this. It's been this way since before Server 2012. Remember to reverse this/turn off xp_cmdshell at the end of your procedure. – Danny Beckett Jan 27 '13 at 17:36
  • 2
    This will give the error message "The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option." as is. – Martin Smith Jan 27 '13 at 17:42
  • @Denny I'm not sure if toggling it on/off inside a procedure is a good idea. What if two users are running the procedure, and this on/off switch overlaps? – Aaron Bertrand Jan 27 '13 at 18:06
  • See @AaronBertrand's answer for the full solution. – Danny Beckett Jan 27 '13 at 18:30
  • You can do this from SQL Server Management Studio as follows: Right-click the server, and choose Facets Select Facet Surface Area Configuration Set property XPCmdShellEnabled to True – Vishe Nov 15 '16 at 11:00