Início / dba / Perguntas / 194136
Accepted
DBADon
Asked: 2017-12-29 13:24:25 +0800 CST

Desabilitar o Agent XPs faz com que sp_add_job (e sp_verify_job) apresente erro com Msg 15281

  • 772

Desabilitar o Agent XPs está impedindo que sp_add_job seja executado com êxito.

Durante a manutenção, nosso software está desabilitando os Agent XPs. Isso precisa ser corrigido, mas TODOS os nossos outros sites continuam sem problemas.

Fizemos alguns testes entre sistemas, o teste consistindo em: desabilitar o Agent XPs, executar EXEC sp_add_job 'hello there';(Isso cria um trabalho com o nome especificado).

Descobrimos que sp_add_jobfalha neste site solitário, mas em todos os outros lugares o trabalho é adicionado muito bem.

Comparamos o código sp_add_jobentre sites comparáveis ​​e o código é idêntico. As permissões também parecem ser as mesmas.

Tentamos depurar o procedimento executando-o como um script, mas curiosamente isso produziu o mesmo resultado quebrado em todos os sistemas (mesmo aqueles que estão funcionando).

Algo está permitindo sp_add_jobfuncionar bem em qualquer outro lugar, mas impedindo que funcione em um 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

Se você executar o código anexado, o erro da etapa 4:

Msg 15281, Level 16, State 1, Procedure sp_verify_job, Line 2
SQL Server bloqueou o acesso ao procedimento 'dbo.sp_verify_job' do componente 'Agent XPs' porque este componente está desativado como parte da configuração de segurança para este servidor. Um administrador de sistema pode habilitar o uso de 'Agent XPs' usando sp_configure. Para obter mais informações sobre como habilitar 'Agent XPs', consulte "Configuração da área de superfície" nos Manuais Online do SQL Server.

Msg 515, Level 16, State 2, Line 178
Não é possível inserir o valor NULL na coluna 'category_id', tabela 'msdb.dbo.sysjobs'; coluna não permite nulos. INSERIR falha.

É o mesmo erro que estamos recebendo na etapa #2.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Por fim, tentamos descartar e recriar o msdbbanco de dados no servidor em questão e o problema desapareceu.

    • 1

  2. Por algum motivo, a pergunta parece estar faltando a mensagem de erro exata, que é uma informação crucial para a depuração. Se isso pudesse ser adicionado à pergunta, seria muito útil. Mesmo que essa mensagem nunca tenha sido salva em algum lugar e não seja mais possível obtê-la desde msdbque foi recriada, até mesmo uma paráfrase da mensagem de erro ajudaria.

    Tentamos depurar o procedimento executando-o como um script, mas curiosamente isso produziu o mesmo resultado quebrado em todos os sistemas (mesmo aqueles que estão funcionando).

    É ambíguo se "mesmo resultado quebrado" se refere ao erro recebido ao executar o sp_add_jobcódigo como um script em todos os servidores, mas ainda distinto do erro principal relatado aqui ao executar o procedimento armazenado em uma instância, OU se for o mesmo erro entre todas as instâncias que executam o código proc como um script e executam o proc nessa instância.

    No mínimo, posso explicar por que o mesmo código usado no sp_add_jobprocedimento armazenado não funciona quando executado fora do procedimento armazenado: ele está obtendo permissões adicionais por ser assinado pelo ##MS_AgentSigningCertificate##Certificado. Você pode ver a lista de objetos que são assinados e por que eles são assinados, usando a seguinte consulta:

    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];

    Supondo que você esteja recebendo o mesmo erro (ou semelhante) executando o procedimento armazenado nessa instância, é possível que alguém tenha alterado sp_add_job(pelo menos executado ALTER PROCEDUREmesmo que o código permanecesse o mesmo) ou executado sp_refreshsqlmodulenele, o que descarta a assinatura e, portanto, descarta essas permissões extras associadas a esse Certificado.

    É claro que, como msdbjá foi recriado, pode não ser possível investigar mais. MAS, nesse caso, SE você tiver um backup dele e puder restaurá-lo, execute a mesma consulta mostrada acima e veja se sp_add_jobaparece. Presumo que não, e isso só pode ser devido às duas razões que mencionei acima, ou alguém explicitamente descartando a assinatura (mas isso parece bastante improvável, eu acho).

    Agora que a mensagem de erro foi adicionada à pergunta, ela parece estar relacionada às permissões. Se você tiver uma instância com a qual possa testar ( msdbpode ser restaurada / recriada facilmente), tente reproduzir o erro executando primeiro sp_add_jobpara garantir que funcione e execute:

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

    e tente sp_add_jobnovamente para ver se você recebe o mesmo erro "Agent XPs".

    Também pode ser que tenha sp_verify_jobsido alterado, e não sp_add_job, já que "verify" é o mencionado na mensagem de erro, então tente esse também.

    • 1

relate perguntas