Database.ca SQL Server Blog

My Blog

Saturday, February 22 2014

Keywords: Change SQL Server Configuration, sp_reconfigure, SQL 15281 Error, Turn on Agent XP's, Turn on 'xp_cmdshell'

Change SQL Server Configuration Using sp_reconfigure, Usually Subsequent to a SQL 15281 Error

Off the box, latest Microsoft SQL Server versions come out a bit too uptight when it comes down to security...which is a good thing. Certain features such as SQL Agent extended stored procedures (Agent XP's), and xp_cmdshell are turned off by default.
 
Per example you'd get that in SQL Server 2012, if you right-click on SQL Agent in SSMS and then choose Properties:

 

Change SQL Server Configuration,  sp_reconfigure, SQL 15281 Error, Turn on Agent XP's, Turn on 'xp_cmdshell'

What you should know, is that you can see the current and future value of any of these configurations at any time but simply running:
 
use master
go
sp_configure
go

 

Change SQL Server Configuration,  sp_reconfigure, SQL 15281 Error, Turn on Agent XP's, Turn on 'xp_cmdshell'

 

Change SQL Server Configuration,  sp_reconfigure, SQL 15281 Error, Turn on Agent XP's, Turn on 'xp_cmdshell'

You could then taken any of the values mentioned in the above 'name' column, and reconfigure it:

 

Change SQL Server Configuration,  sp_reconfigure, SQL 15281 Error, Turn on Agent XP's, Turn on 'xp_cmdshell'

use master
go
sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'Agent XPs', 1
go
reconfigure with override
go

 

Or per example:
 
use master
go
sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'xp_cmdshell', 1
go
reconfigure with override
go

 

 

 

 

 

Written by Ramin Haghighat