Decrypting SSIS Passwords

Jason Brimhall shows how to decrypt your Integration Services package’s password if you have a SQL Agent job set to execute that package:

Take note here that I am only querying the msdb database. There is nothing exceedingly top secret here – yet. Most DBAs should be extremely familiar with these tables and functions that I am using here.

What does this show me though? If I have a package that is being run via Agent Job in msdb, then the sensitive information needs to be decrypted somehow. So, in order to do that decryption the password needs to be passed to the package. As it turns out, the password will be stored in the msdb database following the “DECRYPT” switch for the dtutil utility. Since I happen to have a few of these packages already available, when I run this particular query, I will see something like the following in my results.

That’s a clever solution.  I get the feeling that I should be a bit perturbed by how simple this is, but I don’t; the real sensitive data is still secure.

Related Posts

Parallel Execution With SSIS Framework Community Edition

Andy Leonard has a great post on parallel execution with SSIS: Sit a spell and let Grandpa Andy tell yall a story about some data integratin’. Suppose for a minute that you’ve read and taken my advice about writing small, unit-of-work SSIS packages. I wouldn’t blame you for taking this advice. It’s not only online, […]

Read More

Active Directory Management With Powershell

Jana Sattainathan walks us through a few AD management scenarios using the Powershell Active Directory module: If you are an AD admin, you are very likely a pro at managing AD group membership but for mere mortals, this can be a tedious task. Please read on to find out what I am talking about and […]

Read More


October 2017
« Sep Nov »