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:
[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:
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:
[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]
“Well, that’s really secure”, I thought to myself.