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

I agree with Michael's comment about a password guessing or dictionary attack as being a good approach.

I was just about to also suggest using a cloud computing environment like EC2 to divide and conquer ... but then I realized you are stuck on windows!

Answer 2

Just, open the package in notepad and change the protection level from 2 to 1

<DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property>

Answer 3

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.

Answer 4

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 5

Change the Protection level of package to encrypt with sensitiveKey and all will work fine. :)

Answer 6

It seems that the package was also stored on SQL Server (msdb database), exporting it from Integration Services into the file system allows us to open it (with a warning about losing sensitive data). This solution works perfectly for this particular situation; we mainly needed to know what happens in these packages.