Raymond Lewallen

Framework Design, Agile Coach, President Oklahoma City Developers Group, Microsoft MVP C#, TDD, Continuous Integration, Patterns and Practices, Domain Driven Design, Speaker, VB.Net, C# and Sql Server

Sql Server 2005: xp_cmdshell is turned off? what?

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.

Published May 10 2005, 05:38 AM by Raymond Lewallen

Filed under: Sql Development

Comments

Darrell said:

They always told you to turn it off for security reasons, but Microsoft never did it because they were afraid of breaking existing apps. I guess they went ahead and took the plunge!

# May 11, 2005 6:05 AM

Shimi said:

The only problem is that you have to find about all these "great" things of SQL 2005 only after something does not work. Or not. Anybody has a source for all these little things we need to know about SQL2005?

# December 18, 2006 12:19 AM

TrackBack said:

# March 27, 2007 9:14 PM

Megha Jain said:

I want to know enaBLE XP_CMDSHELL IN SQL server 2005. I want to copy database file from one server to another through dos command'Xcopy'

# December 29, 2007 4:42 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

About Raymond Lewallen

Working primarily in the public sector during his career, Raymond has designed and built several high profile enterprise level applications for all levels of the government. Raymond now works as a solutions architect for EMC. Raymond is an agile coach, Microsoft MVP C# and also president of the Oklahoma City Developers Group and Oklahoma Agile Developers Group. Raymond spends a lot of his time learning and teaching such things as Test Driven Development, Domain Driven Design, Design Patterns and Extreme Programming practices and principles, to name a few. Raymond is also an advocate of Alt.Net. Raymond is primarily a framework guy, so don't ask him anything about UI :)