CODEWITHSUNDEEP
xmlencryptionssis
user2607902
user2607902

Reputation: 13

Decrypt XML in SSISDB

Our current packages are stored in msdb for deployment and we have a job that allows developers to determine what objects are used in what processes by querying an in-house developed job that populates this table. This is a 2008 SSIS job. We are in the process of converting this job to 2016 and that will use SSISDB to manage the package data. As you know, SSISDB has the XML encrypted in the table.

I did search on SO and didn't find much (really didn't find much anywhere) on a successful decryption of the XML data using the function

internal.decrypt_binarydata() which takes: @algorithmName, @key, @IV, @binary_value

Has anyone successfully decrypted the XML data that is stored in SSISDB and if so, could you please share some code samples?

Upvotes: 1

Views: 846

Answers (1)

billinkc
billinkc

Reputation: 61201

The msdb stores packages in dbo.sysssispackages (or sysdtspackages90 if you're still on 2005)

The SSISDB does not store packages. It stores .ispac files which contain Project level connection managers, the project parameter files, a manifest and packages. The ispac is just a zip file. It's stored in SSISDB.internal.object_versions.

To work with that data, you'll need to open the correct key(s) or impersonate a user that has that permission and then look at the approaches in Get Package XML from SSIS Catalog with PowerShell

Upvotes: 1

Related Questions