Microsoft SQL Server is a great product to work with. Especially from the release of version 2005 and now 2008. For me, it is the standard for all kinds of databases. The Object explorer, Tuning Advisor, the Query Analyser and T-SQL statements work great, and are a joy to work with. When it goes wrong somewhere, it is mostly a syntax error, program(bug) error or a user error that causes the problem. Stable as it is, I can recomment the use of it to anyone. In this articles I like to share with you, some errors and "best practices" that came up during my work several times. So, initially set up was for my own references, now I share it with you. Enjoy these articles!! Due to the fact that a big percentage of visitors of this page are international based, this article is in the english language.
Error message 15281 SQL Server Blocked Access To Procedure Sys.Xp_Cmdshell:
This error can occur if you try to run a commandline expression from, for instance a stored procedure, and the Command Shell is disabled. This Command Shell is disabled by default in SQL Server 2005 and 2008, and will be disabled in all future versions. It is, or can be a security issue, and as Microsoft is becoming more and more aware of this, the default setting is disabled in the versions 2005 and 2008. However, you can turn it on! This is often very handy, to make (older) (and present) stored procedures work in a (production) environment. Often you may receive an error something like this.
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.
You can resolve this error in 2 different ways, let me state them.
Keep in mind that your opening a security setting that can compromise your database if you do not create other security measurements.
Open the SQL management studio and create a new query. The default database is the master, thats good, otherwise select it.
Copy/paste the following commands and check/run it.
Exec Master.dbo.Sp_Configure 'Show Advanced Options', 1
Exec Master.dbo.Sp_Configure 'XP_CmdShell', 1
In the result section there will be a message like this:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.
As you allready gave the reconfigure command after the execution line, you can ignore this message and go on with your statement or procedure that did not work before.
Method 2: (the GUI way)
You can get the same result by opening a GUI in the "Surface Area Configuration Tool". Therefore you take the following steps:
This is almost the same for SQL 2008, I think you'll figure it out yourself.
If you have any questions about this article, or want a correction on it, do not hesitate to contact me.
Printscreen: Not available.
Ben Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer for Qurius Customer Care in the Netherlands, specialized in System Center Solutions.
Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.