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

Argenis — Thu, 10 Nov 2011 05:17:00 GMT

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…

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:

SELECT CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max))

    FROM msdb.dbo.sysssispackages p

    WHERE p.name = 'LePackage'

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

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:

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:

SELECT sjs.command

    FROM msdb.dbo.sysjobs sj

    JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id

    WHERE sj.name = 'Run LePackage'

The result:

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

Cheers,

-Argenis

Argenis Fernandez : Lost Passwords

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