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?

Abiogenesis

Ouch, I would be interested to know the answer to this also as I suspect it cannot be done without some sort of hack. Not that is much help to you now but you may wish to consider using a Password Safe/Key Manager internally within your organisation to avoid this issue in the future. – Geotaxis

Good Question! Did you guys use any form of source safe on this? You may be able find an unencrypted version there and some developers even put a file up with the password... – Negrillo

Answer 1

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.

Concinnous

Answer 2

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

Lousewort

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.

Diphenylhydantoin

The difference between my query and the one below is you have to put the job step name in instead of the package name. Mine will search with the package name. – Diphenylhydantoin

Answer 4

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

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

Coccidioidomycosis

Answer 5

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.

Abiogenesis

Answer 6

Here are the steps to reset the password of a SSIS package

1. Edit SSIS project file
2. Change the Protection Level to EncryptAllWithPassword and update new password
3. Save the project file.
4. Change the protection level of each package to EncryptAllWithPassword
5. Save all and recompile solution

Renfro

Answer 7

I modified @Venky solution slightly to:

SELECT sjs.command
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE command like '%decrypt%'

This showed me all packages with passwords.

Insertion

Answer 8

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!

Cerium

EC2 does run Windows too. (aws.amazon.com/windows) And then there is Azure! (microsoft.com/azure) – Concinnous

so there you go! Rent a 1000 machines and crack the password. (and your boss's password, that hated co-worker's password, ... ) – Cerium

Answer 9

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

Coom