Tale of an Encrypted SSIS Package in msdb and a Lost Password
- by Argenis
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
