XML FeedsIf 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.
thanks! Script worked like a charm in 2008 - saved for future reference. 
thanks very musch ...!!! 
Thanks! 
Can you tell me where to run the script for enabling the xp_cmdshell? I previously used the Surface Area Config with 2005...but we upgraded to SQL 2008 Express and that is no longer there. I dont know where to run the TSQL from? 
Denise, you need to run it from a query window 
Thanks mate, that helped heaps 
Hi, I have just installed SQL 2008 on my new machine after using SQL 2005 on my old one. I was trying to connect to a remote instance of 2005 on another server from my new 2008 instance and it was erroring as you described, even though "Allow Remote Connections to this server" was checked on my instance properties. 
Thank you very much. This worked like a charm for me. 
This was great I'm and Engineer, not a DBA, that works with Control systems. We are cutting cost by using 2008 express as 10GB is more than enough for Data Storage. Since Surface Area Configurator (What ever the Hell that is) is not with Express I was glad to find the info to set XP_cmd and ad Hoc. Thank you very much. 
Thanks a TON!!! i was wondering how to setup the XP_cmdshell. I had provided bulkadmin access to the ID and it was still not working. Thanks for the quick win solution. 
Hi,
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.