Tale of an Encrypted SSIS Package in msdb and a Lost Password

Yesterday a Developer at work asked for a copy of an SSIS package in Production so he could work on it (please, dear Reader – withhold judgment on Source Control – I know!). I logged on to the SSIS instance, and when I went to export the package…

LePackage_Password

Oops. I didn’t have that password. The DBA who uploaded the package to Production is long gone; my fellow DBA had no idea either – and the Devs returned a cricket sound when queried.

So I posed the obligatory question on #SQLHelp and a bunch of folks jumped in – some to help and some to make fun of me (thanks, @SQLSoldier @crummel4 @maryarcia and @sqljoe). I tried their suggestions to no avail…even ran some queries to see if I could figure out how to extract the package XML from the system tables in msdb:

[sql]SELECT CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max))[/sql] [sql] FROM msdb.dbo.sysssispackages p[/sql] [sql] WHERE p.name = ‘LePackage'[/sql]

 

This just returned a bunch of XML with encrypted data on it:

EncryptedXML

I knew there was a job in SQL Agent scheduled to execute the package, and when I tried to look at details on the job step I got the following:

RunLePackage

Not very helpful.

The password had to be saved somewhere, but where??

All of a sudden I remembered that there was a system table I hadn’t queried yet:

[sql]SELECT sjs.command[/sql] [sql] FROM msdb.dbo.sysjobs sj[/sql] [sql] JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id[/sql] [sql] WHERE sj.name = ‘Run LePackage'[/sql]

The result:

ResultsLePackage

“Well, that’s really secure”, I thought to myself.

Cheers,

-Argenis

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.