SQL Agent xp's enable/disbale

  • HI,

    we are using SQL Server 2005 std edition cluster active/passive server for out production activities.

    Today I tested a thirdparty DB scan tool for vulnerabilities. Below is the one

    -- The following statement is to fix a vulnerability within the following check:

    -- Agent XPs enabled

    USE master

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'Agent XPs', '0'

    RECONFIGURE

    Just wondering what Microsoft recommends on this. do I need to disable it, any impact on SQL Server/Agent? Please advice. THank you.

  • AgentXP's should not be disabled. They are required for the nodes of the SQL Sever Agent to appear in the SSMS. If you disable them you can't manage Agent nodes through SSMS.

    Thank You,

    Best Regards,

    SQL Buddy

  • It's more of a best practice "if you ain't gonna use it, don't turn it on". DoD security checklist have the same thing. "Disable this feature if not required."

    The "required" portion of this is if you want to have the ability to schedule ANYTHING within SQL Server, you will need the AgentXPs enabled.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • If I understood correct...

    if we disable agent xps , only thing is SQL Agent node/group is not available in SSMS.

    bUT agent service will run as normal, no issue with jobs.

    Please correct me if I am wrong

    one more thing I disabled agent xp's in my local machine to test using below command

    USE master

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'Agent XPs', '0'

    RECONFIGURE

    after that I successfully restarted the sql agent and everything was looking good.

    when I ran the sp_configure

    agent xp 's config value is 1

    not use why it changed to 1 again

  • Yes you are correct if you disable it the extended stored procedures for SQL Server Agent are not accessible, then you will see the little red X on the node within SSMS.

    If you start the SQL Server Agent service from within SSMS it will automatically enable Agent XPs on the server. http://msdn.microsoft.com/en-us/library/ms178127.aspx

    Which to just let you know this is not to say you still can't automate things with SQL Server, if you disable Agent XPs. You just can do it IN SQL Server, you could still use Scheduled Task or some 3rd party tool. It is just much more simple and manageable to do it with SQL Server Agent.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Shawn is correct.

    SQLBuddy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply