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

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.

Answer 2

I don't think there is any way to recover package if EncryptAllWithPassword is used, the whole package is encrypted, and can't be decoded without password. One can try of course password guessing or dictionary attacks hoping the developer used weak password.

If it is EncryptSensitiveWithPassword - you can open and then just retype the connection string passwords.

Answer 3

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!