Free SQL Help for Devs, DBAs, and the Curious

Understanding the concept of SSIS Package Protection Levels can be a bit challenging for many database administrators. However, a comprehensive understanding of this concept is crucial for the effective management and security of your database. In this blog post, we will demystify the SSIS Package Protection Levels, using examples from SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse.

SSIS Package Protection Levels are a series of settings that control the sensitivity of certain types of data in your SSIS packages. There are six different protection levels, each offering a different level of security. They include; DontSaveSensitive, EncryptSensitiveWithUserKey, EncryptSensitiveWithPassword, EncryptAllWithUserKey, EncryptAllWithPassword, and ServerStorage.

The DontSaveSensitive option, as the name suggests, will not save any sensitive data. This means that all sensitive data will be removed from the package when it is saved. This setting is particularly useful when you want to prevent any sensitive data from being stored in the package file. Here is an example of how this can be set up in T-SQL:

USE [SSISDB]
GO
ALTER PROCEDURE [catalog].[set_package_protection]
@folder_name nvarchar(128),
@project_name nvarchar(128),
@package_name nvarchar(260),
@protection_level smallint
AS
BEGIN
...
END

EncryptSensitiveWithUserKey, as the name suggests, encrypts all sensitive data with a key that is associated with the user profile of the person who created the package. This means that only the person who created the package can open it without any issues.

The EncryptSensitiveWithPassword option encrypts all sensitive data using a password. This means that anybody with the password can open the package without any issues. This setting is particularly useful when you want to share the package with others but still want to protect sensitive data. Here is how you can set it up in MySQL:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

EncryptAllWithUserKey and EncryptAllWithPassword options work similarly to EncryptSensitiveWithUserKey and EncryptSensitiveWithPassword respectively, but they encrypt the entire package instead of just the sensitive data. ServerStorage, on the other hand, only works in SQL Server. It stores the package with all sensitive data in SQL Server, and only users with specific roles can access it.

Understanding the SSIS Package Protection Levels is crucial for the effective management and security of your database. By properly using these settings, you can ensure that your sensitive data is safe and only accessible by the right people. Remember that each protection level has its advantages and disadvantages, so choose the one that best suits your needs.