SQL Server Agent Service - SSIS package scheduling not working

Question

We have a local admin privilege to our VPN users and we have created SSIS packages but not able to run it through SQL server agent services as its not allowing to run throwing error being non-administrator .

ERROR:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

please help!!.

score 0 · Answer 1 · answered Apr 12 '16 at 16:10

the first thing I would do is actually verify if you have the right password there, as the message says.

if that is not the case, then I would as a rule create a proxy to run any packages from inside sql server.

make sure that the right permissions are also granted in any folders/files/databases/servers that the package needs to access/read/modify

I have an example here as how to create a proxy account to run a package from inside sql server.

there are comments on the script, that may help you.

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

after that is done on the job in sql server, you need to specify run as yourproxy.

just like you can see on the example below:

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

SQL Server Agent Service - SSIS package scheduling not working

1 Answers1