☰

SQL Server 代理服务-SSIS 包调度不起作用

tag-icon • tag-icon sql-server ssis
SQL Server 代理服务-SSIS 包调度不起作用

我们对 VPN 用户拥有本地管理员权限，并且我们已经创建了 SSIS 包，但无法通过 SQL 服务器代理服务运行它，因为它不允许以非管理员身份运行并抛出错误。

错误：

无法解密受保护的 XML 节点“DTS:Password”，错误为 0x8009000B“密钥在指定状态下无效”。您可能无权访问此信息。当出现加密错误时，会发生此错误。请验证是否有正确的密钥。

请帮忙！！。

答案1

我要做的第一件事实际上是验证您是否输入了正确的密码，正如消息所说的那样。

如果不是这种情况，那么我通常会创建一个代理来运行 SQL 服务器内部的任何包。

确保在包需要访问/读取/修改的任何文件夹/文件/数据库/服务器中也授予了正确的权限

这里有一个例子，说明如何创建代理帐户来从 SQL 服务器内部运行包。

脚本上有评论，可能会对您有所帮助。

-- script for creating a proxy in order to run a SSIS package
-- marcelo miorelli
-- 15-aug-2014

--==============================================================================================
-- server is REPLON1.dev.mycompany.local 


   PRINT @@SERVERNAME
--==============================================================================================


-- grant the db_ssisoperator role to the user - otherwise it cannot find the package later on
USE MSDB
GO
EXEC sp_addrolemember N'db_ssisoperator', N'DEV\QSNRestrictionUser'
GO

--Script #1 - Creating a credential to be used by proxy
USE MASTER
GO 
--Drop the credential if it is already existing 
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'QSNRestrictionCredentials') 
BEGIN 
DROP CREDENTIAL [QSNRestrictionCredentials] 
END 
GO 
CREATE CREDENTIAL [QSNRestrictionCredentials] 
WITH IDENTITY = N'DEV\QSNRestrictionUser', 
SECRET = N'1House?' 
GO


--Script #2 - Creating a proxy account 
USE msdb
GO 
--Drop the proxy if it is already existing 
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'QSNRestrictionProxy') 
BEGIN 
EXEC dbo.sp_delete_proxy 
@proxy_name = N'QSNRestrictionProxy' 
END 
GO 
--Create a proxy and use the same credential as created above 
EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'QSNRestrictionProxy', 
@credential_name=N'QSNRestrictionCredentials', 
@enabled=1 
GO 
--To enable or disable you can use this command 
EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'QSNRestrictionProxy', 
@enabled = 1 --@enabled = 0 
GO

--Script #3 - Granting proxy account to SQL Server Agent Sub-systems 
USE msdb
GO 
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11 
EXEC sp_enum_sqlagent_subsystems 
GO

--Grant created proxy to SQL Agent subsystem 
--You can grant created proxy to as many as available subsystems 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'QSNRestrictionProxy', 
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image 
GO 
--View all the proxies granted to all the subsystems 
EXEC dbo.sp_enum_proxy_for_subsystem


--Script #4 - Granting proxy access to security principals 
USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'QSNRestrictionProxy'
,@login_name=N'DEV\QSNRestrictionUser' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO

在 SQL Server 中完成作业后，您需要指定以 yourproxy 身份运行。

就像您在下面的例子中看到的那样：

-- change job to be run under the proxy account
-- marcelo miorelli
-- 15-08-2014

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_jobstep @job_name=N'run package QSN_Removals ever 2 hours', @step_id=1 , 
            @proxy_name=N'QSNRestrictionProxy'
    GO
    EXEC msdb.dbo.sp_update_job @job_name=N'run package QSN_Removals ever 2 hours', 
            @owner_login_name=N'DEV\QSNRestrictionUser'
    GO

相关内容