THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Argenis Fernandez

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:

 

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:

 

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:

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:

ResultsLePackage

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

Cheers,

-Argenis

Published Wednesday, November 09, 2011 10:17 PM by Argenis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Robert L Davis said:

I'm glad I was able to help out! Also glad that you weren't screwed. Disappointed to see that the password is in plain text. You should file a Connect item for it.

November 10, 2011 12:49 AM
 

Greg M Lucas said:

A very usefult tip - but frightening at the same time

November 10, 2011 3:58 AM
 

Stefan K. said:

Very usefull blog post!

November 10, 2011 10:13 AM
 

D. Pendleton said:

Remember, the only true security is physical security...

November 10, 2011 3:26 PM
 

Anup Warrier said:

LOL!!! Excellent post

January 11, 2012 5:00 PM
 

Cybrduck said:

Thanks a million!  Our project is saved.

January 13, 2012 11:24 AM
 

Bharath K Mohan said:

Thanx a lot. Excellent work around..

December 6, 2012 2:25 AM
 

Alma said:

You are awesome!!!

January 15, 2013 8:35 PM
 

Joe Koppin said:

Thank you for this, we have been using a 2005 package and every developer has been here less than 2 yrs so we were stuck until we stumbled upon this, thanks a bundle!

March 15, 2013 11:00 AM
 

Bob said:

You, sir, kick ass!

May 7, 2013 10:43 AM
 

John J said:

Thanks a lot, very usefull!

November 13, 2013 3:53 PM
 

Radhika said:

Thanks a bunch...This was really very helpful...Saved my time..

Thanks again :)

December 5, 2013 4:49 AM
 

Tim said:

Thanks! You just saved me!

December 23, 2013 3:19 PM
 

Krishna said:

This one works very well. But still, if your DBA has restricted the select permission on the sysjobs or sysjobsteps table, then you would not be able to do much about it

In most of the cases, DBA's do restrict access to system databases for security reasons. Same as my case

Thanks for the suggestion, though :-)

December 30, 2013 1:19 AM
 

Argenis said:

@Krishna: yeah, this kinda assumes that you have access to that table. We don't want just anybody to be able to see these passwords in the clear, do we? Still, I would argue that the credential system is a much better mechanism to safeguard this information - and that's what the SQL Server should use.

December 30, 2013 3:40 PM
 

John Serna said:

Really helpfull, and really "secure" protection.. LOL

February 25, 2014 8:07 AM
 

Andy W said:

bacon saved! Thanks.

April 22, 2014 7:06 AM
 

Hamza Bohari said:

Thanks Argenis

Excellent and very helpful, but again it's SSIS password not protected

April 28, 2014 8:15 AM
 

Aric Friesen said:

Very nice.  I was in the same boat and this allowed me to find the missing password.  Also your post was very well written and the screenshots helped to make sure we were doing the same thing.

May 8, 2014 2:12 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement