Sql-Server

禁用代理 XP 會導致 sp_add_job(和 sp_verify_job)出錯並顯示 Msg 15281

  • January 3, 2018

禁用代理 XP 會阻止 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 步收到的錯誤相同。

最終,我們嘗試msdb在有問題的伺服器上刪除並重新創建數據庫,問題就消失了。

由於某種原因,該問題似乎缺少確切的錯誤消息,這是調試的關鍵資訊。如果可以將其添加到問題中,那將是最有幫助的。即使該消息從未保存在某處並且由於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 PROCEDURE``sp_refreshsqlmodule

當然,msdb由於已經重新創建,可能無法進一步調查。但是,在這種情況下,如果您有它的備份並且可以恢復它,請執行上面顯示的相同查詢並查看是否sp_add_job顯示。我認為它不會,這只能是由於我上面提到的兩個原因,或者有人明確放棄了簽名(但我認為這似乎不太可能)。


現在錯誤消息已添加到問題中,它似乎與權限相關。如果您有一個可以測試的實例(msdb可以輕鬆恢復/重新創建),那麼您可以嘗試通過首先執行來重現錯誤sp_add_job以確保它有效,然後執行:

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

然後再試sp_add_job一次,看看你是否得到同樣的“Agent XPs”錯誤。

也可能是sp_verify_job被改變了,而不是sp_add_job,因為“驗證”是錯誤消息中提到的,所以也試試那個。

引用自:https://dba.stackexchange.com/questions/194136