How to enable 'Web Assistant Procedures' using sp_configure in order to access to procedure 'sys.xp_makewebtask'

Hello all,

I was trying to run the sp_makewebtask stored procedure as I have copied down the t-sql script code below.

sp_makewebtask
@outputfile = 'c:\departments-xmlfile.xml',
@query = 'SELECT * FROM HumanResources.Department',
@templatefile = 'c:\template.tpl'


The above sql server code generated the following error failing to execute

Msg 15281, Level 16, State 1, Procedure xp_makewebtask, Line 1 SQL Server blocked access to procedure 'sys.xp_makewebtask' of component 'Web Assistant Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Web Assistant Procedures' by using sp_configure. For more information about enabling 'Web Assistant Procedures', see "Surface Area Configuration" in SQL Server Books Online.

In order to solve this problem and make the blocked sql procedure xp_makewebtask accessible by sql server programmers run the sp_configure procedure

sp_configure

This will list a part of all configurable settings in the SQL Server instance


allow updates
backup compression default
clr enabled
cross db ownership chaining
default language
filestream access level
max text repl size (B)
nested triggers
remote access
remote admin connections
remote login timeout (s)
remote proc trans
remote query timeout (s)
server trigger recursion
show advanced options
user options


If the server option "show advanced options" parameter has a run_value of 0 then as a sql developer you can run sp_configure sql command to set 'show advanced options' to value 1 to view all the configurable settings.

sp_configure 'show advanced options', 1

The output of this t-sql command is as follows:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

So, run the Reconfigure command to update the settings value.

RECONFIGURE

If you now call again the sp_configure stored procedure, this time 66 SQL Server configurable options will be listed as follows

Ad Hoc Distributed Queries
affinity I/O mask
affinity mask
Agent XPs
allow updates
awe enabled
backup compression default
blocked process threshold (s)
c2 audit mode
clr enabled
common criteria compliance enabled
cost threshold for parallelism
cross db ownership chaining
cursor threshold
Database Mail XPs
default full-text language
default language
default trace enabled
disallow results from triggers
EKM provider enabled
filestream access level
fill factor (%)
ft crawl bandwidth (max)
ft crawl bandwidth (min)
ft notify bandwidth (max)
ft notify bandwidth (min)
index create memory (KB)
in-doubt xact resolution
lightweight pooling
locks
max degree of parallelism
max full-text crawl range
max server memory (MB)
max text repl size (B)
max worker threads
media retention
min memory per query (KB)
min server memory (MB)
nested triggers
network packet size (B)
Ole Automation Procedures
open objects
PH timeout (s)
precompute rank
priority boost
query governor cost limit
query wait (s)
recovery interval (min)
remote access
remote admin connections
remote login timeout (s)
remote proc trans
remote query timeout (s)
Replication XPs
scan for startup procs
server trigger recursion
set working set size
show advanced options
SMO and DMO XPs
SQL Mail XPs
transform noise words
two digit year cutoff
user connections
user options
Web Assistant Procedures
xp_cmdshell


Now "Web Assistant Procedures" SQL Server Instance option is displayed. Run the below sql database commands

sp_configure 'Web Assistant Procedures', 1 -- Configuration option 'Web Assistant Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install. RECONFIGURE

sp_makewebtask
@outputfile = 'c:\myxmlfile.xml',
@query = 'SELECT * FROM HumanResources.Department',
@templatefile = 'c:\template.tpl'




This time sp_makewebtask is executed successfully.

Command(s) completed successfully.