Agent XPs option set to 0 after a SQL Server engine service restart

Question

Is the behavior in the title a bug? Why the option gets set back to zero if it was explicitly set up by DBA in the past to allow SQL Server Agent to function properly?

See some more details here:

The Agent XPs option is disabled by default in SQL Server for security reasons, which also disables the SQL Server Agent access on SSMS. Agent XPs option disabled doesn’t mean that SQL Agent services are stopped.

However, most of the Agent functionality does not work when Agent XPs is disabled. For this reason, this setting is enabled so job schedules can run fine.

SQL Server Service restarts should not interfere with the Agent Service or Agent XPs option. The Agent XPs option should only change during restarts if explicitly specified in the SQL Server Service “Startup Parameters” in SQL Server Configuration Manager.

https://www.mssqltips.com/sqlservertip/2729/how-to-start-sql-server-agent-when-agent-xps-show-disabled/

https://dataginger.com/2012/09/19/agent-xps-server-configuration-option/

Thanks so much,

Regards,

Marcelo.

Answer 1

I don't know what might be going on. I note that on three instances on my machine, the config parameter is 1, and I don't think I've ever changed it explicitly. Agent is running on one of the instance, but not the other two.

I kind of suspect that there is some local lockdown software that might be sneaking in.