This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Sql Server 2005: xp_cmdshell is turned off? what? Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

So if you have played around much with Sql Server 2005, you may have noticed something peculiar.  Ok, several things that are peculiar, but I’m only going to mention one.  Have you tried to execute xp_cmdshell?  When you do, you’ll get the following message from the server:

Msg: I don't think so you fool

Msg 15501, Level 16, State 1, Procedure xp_cmdshell, Line 1
This module has been marked OFF. Turn on 'xp_cmdshell' in order to be able to access the module."

Whoa...never seen that one before.  Interesting.  I suppose I’ll just turn it ON, but it took me a little bit of searching to figure that one out too.  To turn it on, there is a “Surface Area Configuration” tool (sounds geometryish) in Microsoft Sql Server programs group that will let you configure this.  You can also execute the following code, which is easier.  I didn’t use the GUI tool, so I don’t have any screenshots or anything, but I read that is where you can configure it if you’re not t-sql kinda person.

Turn it ON

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

And that will enable xp_cmdshell.  So it appears in order to further their efforts in making their server products more secure, Microsoft has graciously turned off xp_cmdshell by default.  Not a bad idea.  If you never use it, then you’ve got a little bit of added security by default.  I guess if you can figure out how to turn it on, then you know enough about Sql Server to know how to secure it too.