Deploying to the SSIS Catalog Changes the Protection Level

I recently answered a question on the SQL Community Slack #ssis channel about SSIS and security. Let me begin by stating that SSIS security is complex, made up of many moving parts, and not trivial. If you struggle with SSIS security configuration, you are struggling with one of the harder parts of SSIS.

Protection Level

Security for SSIS packages and projects is managed by a property called “ProtectionLevel.” Here are some facts about the ProtectionLevel property:

• The project ProtectionLevel setting must match the ProtectionLevel setting for every package in the project.
• If a password is supplied, the same password must be supplied for the project and each package in the project.

You may not like these features, but you will have to engineer data integration solutions for SSIS with these features in mind.

“What Does Protection Level… protect, Andy?”

I’m glad you asked. The ProtectionLevel property defines the method SSIS uses to protect values marked as Sensitive. Connection Manager Password properties are, by default, sensitive. SSIS projects developed using SSIS 2012+ may contain package or project parameters. Parameters have a developer-configurable attribute named Sensitive which, not surprisingly, allows the developer to mark a project or package parameter as Sensitive. Project Connection Managers were added in SSIS 2012 and, as we mentioned earlier, the Connection Manager Password property is Sensitive by default (this, along with the ability to mark project parameters as Sensitive, is why we need a project ProtectionLevel property).

There are six ProtectionLevel settings:

• DontSaveSensitive
• EncryptSensitiveWithUserKey
• EncryptSensitiveWithPassword
• EncryptAllWithPassword
• EncryptAllWithUserKey
• ServerStorage

Now you may have read the list and viewed the image and thought, “Andy, I don’t see ServerStorage in the image.” You may have experienced a moment where you thought, “There are five Protection Levels!” similar to that moment experienced by Jean-Luc Picard at the end of part 2 of Chain of Command (Star Trek: The Next Generation) where he screamed at his Cardassian torturer, “There are four lights!”

You are not wrong. ServerStorage is not there. But ServerStorage is a valid ProtectionLevel. Promise.

“So, where is ServerStorage, Andy?”

You are nailing the good questions today! The ServerStorage ProtectionLevel setting is the default setting for projects and packages deployed to the SSIS Catalog. When you deploy an SSIS package or project, the Integration Services Deployment Wizard decrypts your package and/or project, and then re-encrypts them using ServerStorage. You can see it in this image – step 3 changes the ProtectionLevel property:

What does this encryption look like?

If you click that image to enlarge it, you will see a binary string. You may ask, “Andy, how do you know the binary string is encrypted?” A just question. </GrimaWormtongue> I know because I’ve looked behind the curtain (some). One SSIS Catalog stored procedure used in the Export ISPAC (more on this in a bit…) functionality is SSISDB.internal.get_project_internal. If you script the internal.get_project_internal stored procedure you will note parameters named @key_name and @certificate_name that are used to build dynamic T-SQL statements to open a symmetric key using decryption by certificate – on or about line 69 of the query (depending on how you scripted it).

Yes, it’s binary. But it is also encrypted.

Implications

Now, the important part (and what motivated me to write this post):

If you password-protect your SSIS project, especially using EncryptAllWithPassword to protect intellectual property, your encryption – including your password – disappears forever once you deploy the project to the SSIS Catalog. Anyone with permission to export the project from the SSIS Catalog can see all you desire to hide. How hard is it to export the project? First you need to be a member of the SSIS Admin role. Then, you right-click the project in the SSMS Object Explorer Integration Services Catalogs node, and click Export:

Select a file location and file name for the exported ISPAC file:

Use a zip utility to open the compressed archive (or change the ISPAC file extension to zip, and Windows will decompress it for you):

From here, the package can be copied to disk. Using 7-Zip (or Windows if I changed the extension), I can edit the dtsx file to open the package XML in Notepad:

Here is a side-by-side comparison of the exported XML from the SSIS Catalog and the encrypted file used to develop the same SSIS package:

As you can see, using a password and one of the “WithPassword” Protection Level options will not protect your IP from folks with administrative privileges to your SSIS Catalog.

I can hear you thinking…

“Ok, Andy, You’ve Shown Us the Problem. What’s the Solution?”

I’m glad you asked. To protect intellectual property (IP), I highly recommend you encapsulate said IP in logic that resides outside of your SSIS package. How can you do that? One way is to use a custom assembly coded in .Net. The assembly can be designed to be imported into the Global Assembly Cache (GAC), and from there SSIS can access it from a Script Task (Control Flow) or Script Component (Data Flow Task).

You can also build a custom SSIS task or component to encapsulate your IP and expose configurable properties to developers. I wrote a book about one way to do all the stuff you have to do to build such a task. It’s called Building Custom Tasks for SQL Server Integration Services.

The book is not about the logic required to code your task. The book is about the things you need to know in order to author a custom Visual Studio toolbox item. There’s a non-Production-ready demo that you build throughout the book (which is pretty cool, I think, but I wrote it so I’m biased). I assume you are completely new to Visual Studio software development. The code is in Visual Basic (deal). And I used Visual Studio Community Edition, which is free.

:{>