Disabling Agent XPs causes sp_add_job (and sp_verify_job) to error with Msg 15281

Question

Disabling Agent XPs is preventing sp_add_job from successfully running.

When running through maintenance, our software is disabling Agent XPs. This needs to be fixed, but ALL of our other sites continue on without any issues.

We did some testing between systems, the test consisting of: disable Agent XPs, run EXEC sp_add_job 'hello there'; (This creates a job with the specified name).

We found that sp_add_job fails at this lone site but everywhere else the job gets added just fine.

We compared the code for sp_add_job between comparable sites and the code is identical. The permissions also appear to be the same.

We tried to debug the procedure by running it as a script, but interestingly that produced the same broken result on all systems (even those that are working).

Something is allowing sp_add_job to function just fine everywhere else but preventing it from working at the one site.

use msdb
go

declare @jobname sysname
if exists (select * from sysjobs where name = 'test_with_procedure_12345')
    EXEC msdb.dbo.sp_delete_job @job_name=N'test_with_procedure_12345', @delete_unused_schedule=1

if exists (select * from sysjobs where name = 'test_without_procedure_12345')
    EXEC msdb.dbo.sp_delete_job @job_name=N'test_without_procedure_12345', @delete_unused_schedule=1

exec sp_configure 'agent xps', 0
reconfigure

print '          1. running with procedure' -- Only one site fails here, the rest all work

exec sp_add_job 'test_with_procedure_12345'
select @jobname = name from sysjobs where name = 'test_with_procedure_12345'
print '             job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
print '          2. after running with procedure'

exec sp_configure 'agent xps', 1
reconfigure

print '          3. state reset'

exec sp_configure 'agent xps', 0
reconfigure

print '          4. running without procedure' 


--CREATE PROCEDURE sp_add_job  
DECLARE
  @job_name                     sysname = 'test_without_procedure_12345',  
  @enabled                      TINYINT          = 1,        -- 0 = Disabled, 1 = Enabled  
  @description                  NVARCHAR(512)    = NULL,  
  @start_step_id                INT              = 1,  
  @category_name                sysname          = NULL,  
  @category_id                  INT              = NULL,     -- A language-independent way to specify which category to use  
  @owner_login_name             sysname          = NULL,     -- The procedure assigns a default  
  @notify_level_eventlog        INT              = 2,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_email           INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_netsend         INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_page            INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_email_operator_name   sysname          = NULL,  
  @notify_netsend_operator_name sysname          = NULL,  
  @notify_page_operator_name    sysname          = NULL,  
  @delete_level                 INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @job_id                       UNIQUEIDENTIFIER = NULL ,--OUTPUT,  
  @originating_server           sysname           = NULL      -- For SQLAgent use only  
--AS  
BEGIN  
  DECLARE @retval                     INT  
  DECLARE @notify_email_operator_id   INT  
  DECLARE @notify_netsend_operator_id INT  
  DECLARE @notify_page_operator_id    INT  
  DECLARE @owner_sid                  VARBINARY(85)  
  DECLARE @originating_server_id      INT  

  SET NOCOUNT ON  

  -- Remove any leading/trailing spaces from parameters (except @owner_login_name)  
  SELECT @originating_server           = UPPER(LTRIM(RTRIM(@originating_server)))  
  SELECT @job_name                     = LTRIM(RTRIM(@job_name))  
  SELECT @description                  = LTRIM(RTRIM(@description))  
  SELECT @category_name                = LTRIM(RTRIM(@category_name))  
  SELECT @notify_email_operator_name   = LTRIM(RTRIM(@notify_email_operator_name))  
  SELECT @notify_netsend_operator_name = LTRIM(RTRIM(@notify_netsend_operator_name))  
  SELECT @notify_page_operator_name    = LTRIM(RTRIM(@notify_page_operator_name))  
  SELECT @originating_server_id        = NULL  

  -- Turn [nullable] empty string parameters into NULLs  
  IF (@originating_server           = N'') SELECT @originating_server           = NULL  
  IF (@description                  = N'') SELECT @description                  = NULL  
  IF (@category_name                = N'') SELECT @category_name                = NULL  
  IF (@notify_email_operator_name   = N'') SELECT @notify_email_operator_name   = NULL  
  IF (@notify_netsend_operator_name = N'') SELECT @notify_netsend_operator_name = NULL  
  IF (@notify_page_operator_name    = N'') SELECT @notify_page_operator_name    = NULL  

  IF (@originating_server IS NULL) OR (@originating_server = '(LOCAL)')  
    SELECT @originating_server= UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  

  --only members of sysadmins role can set the owner  
  IF (@owner_login_name IS NOT NULL AND ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME())  
  BEGIN  
    RAISERROR(14515, -1, -1)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1
  END  

  -- Default the owner (if not supplied or if a non-sa is [illegally] trying to create a job for another user)  
  -- allow special account only when caller is sysadmin  
  IF (@owner_login_name = N'$(SQLAgentAccount)')  AND   
     (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)  
  BEGIN  
    SELECT @owner_sid = 0xFFFFFFFF     
  END  
  ELSE   
  IF (@owner_login_name IS NULL) OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME()))  
  BEGIN  
    SELECT @owner_sid = SUSER_SID()  
  END  
  ELSE  
  BEGIN      --force case insensitive comparation for NT users  
    SELECT @owner_sid = SUSER_SID(@owner_login_name, 0) -- If @owner_login_name is invalid then SUSER_SID() will return NULL  
  END  

  -- Default the description (if not supplied)  
  IF (@description IS NULL)  
    SELECT @description = FORMATMESSAGE(14571)  

  -- If a category ID is provided this overrides any supplied category name  
  EXECUTE @retval = sp_verify_category_identifiers '@category_name',  
                                                   '@category_id',  
                                                    @category_name OUTPUT,  
                                                    @category_id   OUTPUT  
  IF (@retval <> 0)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1

  -- Check parameters  
  EXECUTE @retval = sp_verify_job NULL,  --  The job id is null since this is a new job  
                                  @job_name,  
                                  @enabled,  
                                  @start_step_id,  
                                  @category_name,  
                                  @owner_sid                  OUTPUT,  
                                  @notify_level_eventlog,  
                                  @notify_level_email         OUTPUT,  
                                  @notify_level_netsend       OUTPUT,  
                                  @notify_level_page          OUTPUT,  
                                  @notify_email_operator_name,  
                                  @notify_netsend_operator_name,  
                                  @notify_page_operator_name,  
                                  @delete_level,  
                                  @category_id                OUTPUT,  
                                  @notify_email_operator_id   OUTPUT,  
                                  @notify_netsend_operator_id OUTPUT,  
                                  @notify_page_operator_id    OUTPUT,  
                                  @originating_server         OUTPUT  
  IF (@retval <> 0)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1


  SELECT @originating_server_id = originating_server_id   
  FROM msdb.dbo.sysoriginatingservers_view   
  WHERE (originating_server = @originating_server)  
  IF (@originating_server_id IS NULL)  
  BEGIN  
    RAISERROR(14370, -1, -1)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1
  END  


  IF (@job_id IS NULL)  
  BEGIN  
    -- Assign the GUID  
    SELECT @job_id = NEWID()  
  END  
  ELSE  
  BEGIN  
    -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)  
    IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')  
    BEGIN  
      RAISERROR(14274, -1, -1)  
      --RETURN(1) -- Failure  
      --NOTE: replaced with select to run outside of original sp_add_job procedure
      SELECT 1
    END  
  END  

  INSERT INTO msdb.dbo.sysjobs  
         (job_id,  
          originating_server_id,  
          name,  
          enabled,  
          description,  
          start_step_id,  
          category_id,  
          owner_sid,  
          notify_level_eventlog,  
          notify_level_email,  
          notify_level_netsend,  
          notify_level_page,  
          notify_email_operator_id,  
          notify_netsend_operator_id,  
          notify_page_operator_id,  
          delete_level,  
          date_created,  
          date_modified,  
          version_number)  
  VALUES  (@job_id,  
          @originating_server_id,  
          @job_name,  
          @enabled,  
          @description,  
          @start_step_id,  
          @category_id,  
          @owner_sid,  
          @notify_level_eventlog,  
          @notify_level_email,  
          @notify_level_netsend,  
          @notify_level_page,  
          @notify_email_operator_id,  
          @notify_netsend_operator_id,  
          @notify_page_operator_id,  
          @delete_level,  
          GETDATE(),  
          GETDATE(),  
          1) -- Version number 1  
  SELECT @retval = @@error  

  -- NOTE: We don't notify SQLServerAgent to update it's cache (we'll do this in sp_add_jobserver)  

  --RETURN(@retval) -- 0 means success  
  --NOTE: replaced with select to run outside of original sp_add_job procedure
  SELECT @retval as 'errorcode result'
END  

print '          5. after running without procedure'
set @jobname = null
select @jobname = name from sysjobs where name = 'test_without_procedure_12345'
print '             job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
exec sp_configure 'agent xps', 1
reconfigure

If you execute the attached code, the error from step #4:

Msg 15281, Level 16, State 1, Procedure sp_verify_job, Line 2
SQL Server blocked access to procedure 'dbo.sp_verify_job' 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.

Msg 515, Level 16, State 2, Line 178
Cannot insert the value NULL into column 'category_id', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.

Is the same error we are receiving at step #2.

Answer 1

Ultimately, we tried dropping and recreating the msdb database on the server in question, and the problem went away.

Answer 2

For some reason the question seems to be missing the exact error message, which is a crucial piece of information for debugging. If that could be added to the question, it would be most helpful. Even if that message was never saved somewhere and is no longer possible to get since msdb has been recreated, even a paraphrasing of the error message would help.

We tried to debug the procedure by running it as a script, but interestingly that produced the same broken result on all systems (even those that are working).

It is ambiguous whether "same broken result" refers to the error received by running the sp_add_job code as a script across all servers but still distinct from the main error being reported here when running the stored procedure on the one instance, OR if it is the same error between all instances running the proc code as a script and running the proc on that one instance.

At the very least, I can explain why the same code used in the sp_add_job stored procedure does not work when run outside of the stored procedure: it is picking up additional permissions due to being signed by the ##MS_AgentSigningCertificate## Certificate. You can see the list of objects that are signed, and what they are signed by, using the following query:

USE [msdb];

SELECT obj.[name] AS [ObjectName], obj.[type_desc], crt.[name], cp.*
FROM sys.crypt_properties cp
INNER JOIN sys.certificates crt
        ON crt.[thumbprint] = cp.[thumbprint]
INNER JOIN sys.objects obj
        ON obj.[object_id] = cp.[major_id]
ORDER BY obj.[name];

Assuming that you are getting the same (or similar) error running the stored procedure on that one instance, then it is possible that someone altered sp_add_job (at least ran ALTER PROCEDURE even if the code stayed the same), or ran sp_refreshsqlmodule on it, which drops the signature and hence drops those extra permissions that are associated with that Certificate.

Of course, with msdb having been recreated already, it might not be possible to investigate further. BUT, in that case, IF you have a backup of it and can restore it, run that same query shown above and see if sp_add_job shows up. I assume that it won't, and that can only be due to the two reasons I mentioned above, or someone explicitly dropping the signature (but that seems rather unlikely, I would think).

---

Now that the error message has been added to the question, it does appear to be permissions related. If you have an instance that you can test with (msdb can be restored / recreated easily), then you can try to reproduce the error by first executing sp_add_job to ensure that it works, then execute:

USE [msdb];
EXEC sp_refreshsqlmodule N'sp_add_job';

and then try sp_add_job again to see if you get that same "Agent XPs" error.

It could also be that sp_verify_job was altered, and not sp_add_job, since "verify" is the one mentioned in the error message, so try that one as well.