XML Feeds
If you try to execute xp_cmdshell on a fresh install of SQL Server 2005 or 2008 you will be greeted with the following message
Server: Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
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', see "Surface Area Configuration" in SQL Server Books Online.
This is done for security reasons. I see a bunch of Google searches hitting our site with a search like this: How do I enable xp_cmdshell in SQL Server 2005? Well you can do it two ways; one is with a script and the other with the Surface Configuration Tool
Let's start with the Surface Configuration Tool
Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool
Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply
SQL Script
To do it with a script use the one below
- EXECUTE SP_CONFIGURE 'show advanced options', 1
- RECONFIGURE WITH OVERRIDE
- GO
- EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
- RECONFIGURE WITH OVERRIDE
- GO
- EXECUTE SP_CONFIGURE 'show advanced options', 0
- RECONFIGURE WITH OVERRIDE
- GO
Just so you know you can also use the script to enable xp_cmdshell on SQL Server 2008. The Surface Area Configuration Tool in SQL Server 2008 has been replaced by the SQL Server Configuration Manager.
In SQL Server 2005 and 2008 OPENROWSET is also disabled by default, if you try to run an OPENROWSET query then you will see the following message:
Server: Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
To enable OPENROWSET and OPENQUERY you can use the previous script but instead of 'xp_cmdshell' you will use 'Ad Hoc Distributed Queries'. The script to enable Ad Hoc Distributed Queries is below
- EXECUTE SP_CONFIGURE 'show advanced options', 1
- RECONFIGURE WITH OVERRIDE
- GO
- EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1'
- RECONFIGURE WITH OVERRIDE
- GO
- EXECUTE SP_CONFIGURE 'show advanced options', 0
- RECONFIGURE WITH OVERRIDE
- GO
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.