How to Recover or Reset SSIS Package Password?

Question

I have a few SSIS packages that were password-protected (their protection level is apparently EncryptAllWithPassword) by a developer who left the company and can't be reached anymore, and trying to open them gives the following error since the password can't be supplied:

Error loading 'Package.dtsx' : Failed to remove package protection with error 0xC0014037 "The package is encrypted with a password. The password was not specified, or is not correct.". This occurs in the CPackage::LoadFromXML method.

Is there any any way to open these packages? I have access to the administrator account originally used to create these packages and have other packages encrypted by the same person but using a different password that I know.

I have contacted a local Microsoft representative about the issue and so far they have only linked me to a a page describing how to set or change a password, which doesn't help because I need to open the package first or provide the old password. Has anyone been in a similar situation before or knows a way around this issue?

Answer 1

execute the below query

SELECT sjs.command
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sj.name = 'your package name'

In the Result View

check for the text "/DECRYPT", the next following strings are the password

Answer 2

Use this query to find your package password:

SELECT step.command
FROM msdb.dbo.sysjobs job
JOIN msdb.dbo.sysjobsteps step ON job.job_id = step.job_id
WHERE step.command like '%Your Package Name%'

In the results the only column displated is command look through that text for /DECRYPT the next string after it will be the password enclosed in quotation marks.