Thursday, March 24, 2011

SQL Server 2005 Agent XPs disabled issue

Today when I got to work I got an e-mail sent to me through Database mail, notifying me that a number of SQL scheduled jobs failed. That's the message that I found in the logs:

[298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' 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. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

When I logged in to database server, I saw that next to SQL Server Agent there is a note (Agent XPs disabled).

Agent XPs is an option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.

The possible values are:

0, indicating that SQL Server Agent extended stored procedures are not available (the default).

1, indicating that SQL Server Agent extended stored procedures are available.

This setting can be changed without server stop or restart.

Aparently something happened, probably related to the Security policy, that changed the option from 1 to 0. So to resolve the issue I ran the code below to re-enable Agent XPs option by setting it back to 1.

USE master
sp_configure 'show advanced options', 1
sp_configure 'Agent XPs', 1

Another option is to re-enable it through SQL Server Surface Area Configuration tool.

No comments:

Post a Comment