Month: November 2011

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

A (Late) Meme Monday Post: On SQLFamily

 

Yesterday a member of the SQL community who I deeply admire sent me a DM on Twitter asking whether I had done a SQLFamily post for Thomas LaRock’s (blog|@SQLRockstar) Meme Monday for November. I replied that I did not, and I regretted not having done so. A subtle DM followed my response: “Get on it, you have all week”. And indeed I must. So here’s an attempt to express some of my feelings on a community that has catapulted my career like nothing else before I embraced it.

Nanos Gigantium Humeris Insidentes

480px-Library_of_Congress,_Rosenwald_4,_Bl._5r

I stand on the shoulders of giants. My SQLFamily has given me support at all levels. Professionally and personally. There is never a lack of will to help and provide advice to others in this community. And I do my best to help. On #SQLHelp on Twitter, via email, or even on the phone. I expect no retribution, because I know that when and if I do run into problems, my SQLFamily will be there for me.

I have met some of the most humble, dedicated and most professional people in the SQL community. And some of them have pretty big titles: MVPs, MCMs, Regional Mentors, and even leaders of PASS, SQLCAT members, and even PMs and Devs on the SQL Server team.

All are welcome, and that includes YOU!

I have also met some people that are rather reserved and don’t participate as much in the community, for whatever reason. Be as it may, let it be know to all that we are a very welcoming community – heck, some of my closest friends and people I can count on in the community have completely opposite political views. We share one goal: to get better and help others get better.

Even if you are a lurker – my hope is that one day you’ll decide to give back some of what you have learned.

You have to take it to the next level

On one of my previous jobs as an IT Supervisor I used to tell my team all the time about the benefits of continuous education and self-driven learning. Shortly after I left that job, the company went bankrupt and some of my staff got laid off – some without any severance pay whatsoever. I eventually found out that some of them had a really hard time finding another job, because their skills were simply outdated. They had become stale professionals. Don’t be one of them.

If you don’t take advantage of these learning resources, somebody else will – and that person has an advantage over you when applying for that awesome job position that got opened. There’s a severe shortage of good DBAs and DB Devs out there. What’s your excuse for not being excellent?

Even if your knowledge of SQL Server is at the beginner level, really – you have no excuse to get better. Just go to SQLUniversity and learn from there. Don’t get stale!

Thank You

To all of you in the SQL community who put so much time and energy into helping others, my deepest gratitude to you. I can’t wait to meet you all again at the next event and share our SQL stories over a pint of beer (or a shot of Jaeger)

Cheers!

-Argenis