SSRS Connection Manager for 2 separate domain failing when added to SQL Job

1

I am using SQL authentication for a SSIS connection manager for one domain to another. In Visual Studio the connection is working when tested but password does not save. Running the package in VS is working but when creating a SQL job with the package, it fails to run with error

"Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.

Please help. Thank you.

ssis
sql-job
asked on Stack Overflow Jun 6, 2019 by Brian Tran • edited Jun 7, 2019 by Hadi

1 Answer

0

The wrong way to do this is to put your connection string with the user name and password in the Expressions property's Connection String expression. Then change the ProtectionLevel to Dont [sic] Save Sensitive.

While this works, it leaves your password as plain text in the dtsx file.

enter image description here

I believe one correct way to do this is to set the password for the connection string in the SQL Server Agent's New Job step window on the Data Sources tab.

Another way is to change the Protection level to Save with Password and then enter the password in the Job step's Configuration tab. Unfortunately, I don't use SSIS much anymore and haven't tested either of these methods.

https://docs.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-general-page?view=sql-server-2017

MS Forum

answered on Stack Overflow Jun 6, 2019 by Hannover Fist

User contributions licensed under CC BY-SA 3.0