主页 / dba / 问题 / 194136
Accepted
DBADon
Asked: 2017-12-29 13:24:25 +0800 CST

禁用 Agent XPs 会导致 sp_add_job（和 sp_verify_job）出错并显示 Msg 15281

  • 772

禁用 Agent XPs 会阻止 sp_add_job 成功运行。

在维护期间运行时，我们的软件会禁用 Agent XP。这需要修复，但我们所有其他站点都可以继续正常运行，没有任何问题。

我们在系统之间做了一些测试，测试包括：禁用 Agent XP，运行EXEC sp_add_job 'hello there';（这会创建一个具有指定名称的作业）。

我们发现sp_add_job在这个单独的站点失败了，但在其他任何地方都可以很好地添加作业。

我们比较了sp_add_job可比较站点之间的代码，代码是相同的。权限似乎也相同。

我们试图通过将其作为脚本运行来调试该过程，但有趣的是，这在所有系统（即使是那些正在运行的系统）上都产生了相同的错误结果。

有些东西允许sp_add_job在其他地方正常运行，但阻止它在一个站点上工作。

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

如果执行附加的代码，步骤 #4 中的错误：

消息 15281，级别 16，状态 1，过程 sp_verify_job，第 2 行
SQL Server 阻止访问组件“Agent XPs”的过程“dbo.sp_verify_job”，因为此组件作为此服务器安全配置的一部分已关闭。系统管理员可以通过使用 sp_configure 启用“代理 XP”。有关启用“代理 XP”的详细信息，请参阅 SQL Server 联机丛书中的“外围应用配置”。

消息 515，级别 16，状态 2，第 178 行
无法将值 NULL 插入列“category_id”，表“msdb.dbo.sysjobs”；列不允许空值。插入失败。

与我们在第 2 步收到的错误相同。

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    最终，我们尝试msdb在有问题的服务器上删除并重新创建数据库，问题就消失了。

    • 1

  2. 出于某种原因，这个问题似乎缺少确切的错误消息，这是调试的重要信息。如果可以将其添加到问题中，那将是最有帮助的。即使该消息从未保存在某个地方并且由于msdb已重新创建而不再可能获取，即使对错误消息进行解释也会有所帮助。

    我们试图通过将其作为脚本运行来调试该过程，但有趣的是，这在所有系统（即使是那些正在运行的系统）上都产生了相同的错误结果。

    “相同的损坏结果”是指sp_add_job在所有服务器上将代码作为脚本运行时收到的错误，但仍然不同于在一个实例上运行存储过程时此处报告的主要错误，或者如果它是相同的，则不明确将 proc 代码作为脚本运行的所有实例与在该实例上运行 proc 之间的错误。

    至少，我可以解释为什么存储过程中使用的相同代码在sp_add_job存储过程外部运行时不起作用：由于##MS_AgentSigningCertificate##证书签名，它正在获取额外的权限。您可以使用以下查询查看已签名对象的列表以及它们的签名对象：

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

    假设您在那个实例上运行存储过程时遇到相同（或类似）的错误，那么可能有人更改了（即使代码保持不变sp_add_job至少运行了），或者运行了它，这会丢弃签名并因此删除与该证书关联的那些额外权限。ALTER PROCEDUREsp_refreshsqlmodule

    当然，msdb既然已经重现了，可能就没法进一步调查了。但是，在那种情况下，如果您有它的备份并且可以恢复它，请运行上面显示的相同查询并查看是否sp_add_job显示。我假设它不会，这只能是由于我上面提到的两个原因，或者有人明确放弃签名（但我认为这似乎不太可能）。

    现在错误消息已添加到问题中，它似乎与权限相关。如果您有一个可以测试的实例（msdb可以轻松恢复/重新创建），那么您可以尝试通过首先执行来重现错误sp_add_job以确保它有效，然后执行：

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

    然后再试sp_add_job一次，看看您是否遇到相同的“代理 XP”错误。

    也可能是sp_verify_job被更改了，而不是sp_add_job，因为“验证”是错误消息中提到的那个，所以也试试那个。

    • 1

相关问题