How to recover or reset the SSIS package password?

Question

I have several SSIS packages that were password protected (their level of protection is apparently EncryptAllWithPassword) by a developer who left the company and can no longer be reached, and trying to open them gives the following error, because the password can't be delivered:

Error loading "Package.dtsx": Failed to remove the package protection with error 0xC0014037 "The password is encrypted. Password was not specified or was not correct." This happens in the CPackage :: LoadFromXML Method.

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

I contacted the local Microsoft representative on this issue, and so far they have only connected me with a page describing how to set or change the password , which does not 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 to solve this problem?

Answer 1

I don’t think there is a way to restore the packet, if EncryptAllWithPassword is used, the whole packet is encrypted and cannot be decoded without a password. You can try, of course, to guess the password or dictionary attacks, hoping that the developer used a weak password.

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

Answer 2

execute the request below

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 presentation of the results

check the text "/ DECRYPT", the following lines: password

Answer 3

Use this query to find the password for your package:

 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%' 

As a result, the only extruded column is the command to view this text for / DECRYPT on the next line after it is a password enclosed in quotation marks.

Answer 4

It seems that the package was also saved on SQL Server (msdb database), exporting it from Integration Services to the file system allows us to open it (with a warning about the loss of sensitive data). This solution is great for this particular situation; we basically needed to know what was going on in these packages.

Answer 5

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

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

Answer 6

Following are instructions for resetting the SSIS package password.

1. Edit SSIS Project File
2. Change the security level to EncryptAllWithPassword and update the new password
3. Save the project file.
4. Change the security level of each package to EncryptAllWithPassword
5. Save everything and recompile the solution

Answer 7

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

I was just about to suggest using a cloud computing environment such as EC2 to split and win ... but then I realized that you were stuck in windows!

Answer 8

Change the Protection level package to encrypt on sensitiveKey and everything will work fine. :)