Multi-Server jobs - XP Agents

[Peter Schmitz]

I'm still in the middle or adding servers to a multi-configuration setup, and other than the error I ran into earlier, requiring EXECUTE permissions on sp_ssis_getfolder, I enocuntered another error today:

SQL Server blocked access to procedure 'dbo.sp_msx_set_account' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see 'Surface Area Configuration' in SQL Server Books Online. (Microsoft SQL Server, Error: 15281).

To enable this option, on the target server, the 'Agent XPs' option has to be turned on. This is easily done:

sp_configure 'Agent XPs', 1
RECONFIGURE

You can also visually see whether the option is on or off, by the way, as SQL Server Agent will then show it in its description:

 

[Peter Schmitz]

Attempting to change the setting as described above might not be possible on your system off the bat if you did not turn on the ability to show advanced options.If this is the case, you will see the following error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Agent XPs' does not exist, or it may be an advanced option.

Luckily, this, too, is easily done by issuing the command below:

sp_configure 'show advanced options', 1;
RECONFIGURE;