禁用代理 XP 會導致 sp_add_job(和 sp_verify_job)出錯並顯示 Msg 15281
禁用代理 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
,因為“驗證”是錯誤消息中提到的,所以也試試那個。