An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

• The user account that is used to run the package under SQL Server Agent differs from the original package author.
• The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

• The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
• A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
• File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
• A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific.
• A task or a connection manager requires that the current user account has correct permissions.

Back to the top

Back to the top

Method 1: Use a SQL Server Agent proxy account

Back to the top

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage

Back to the top

Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword

Method 4: Use SSIS Package configuration files

Method 5: Create a package template

Back to the top

Steps to reproduce the problem

1. Log in as a user who is not part of the SQLServer2005SQLAgentUser group. For example, you can create a local user.
2. Create an SSIS package, and then add an ExecuteSQL task. Use an OLEDB connection manager to the local msdb file by using the following string: ‘Windows Authentication’ -SQLSourceType: "Direct Input" -SQLStatement: "sp_who"
3. Run the package to make sure that it runs successfully.
4. Notice that the ProtectionLevel property is set to EncryptSensitiveWithPassword.
5. Create a SQL Server Agent job and a job step. In the Run As list, click SQL Server Agent Service to run the job step.

Executed as user: DOMAIN\USERNAME. The package execution failed. The step failed.

Back to the top

Decrypt package secrets

1. Modify the package that you want to encrypt by using a password.
2. Use the Dtutil.exe utility through an Operating System (cmd Exec) SQL Server Agent job step to change the ProtectionLevel property to EncryptSensitiveWithUserKey. This process involves decrypting the package by using the password, and then re-encrypting the package. The user key that is used to encrypt the package is the SQL Server Agent job step setting in the Run As list.
   
   Note Because the key includes the user name and the computer name, the effect of moving the packages to another computer may be limited.

Back to the top

Make sure that you have detailed error information about the SSIS package failure

About SSIS logging

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

About the exec subsystem command and output information

dtexec.exe /FILE 
"C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 
" /CHECKPOINTING OFF  /REPORTING V  /CONSOLELOG NCOSGXMT

Error: 2006-04-27 18:13:34.76
   Code: 0xC0202009
   Source: AgentTesting Connection manager "(local).msdb"
   Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'DOMAINNAME\username'.".
End Error

Error: 2006-04-28 13:51:59.19
   Code: 0xC0016016
   Source:  
   Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". 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.
End Error

Log:
     Name: OnError
     Computer: COMPUTERNAME
     Operator: DOMAINNAME\username
     Source Name: Execute SQL Task
     Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762}
     Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8}
     Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.
     Start Time: 2006-04-27 18:13:34
     End Time: 2006-04-27 18:13:34
End Log

Back to the top

• Scheduling package execution in SQL Server Agent
• Creating SQL Server Agent proxies

Back to the top