SSIS Security (Access control) – Part 3
Control access to content of SSIS packages is vital for enterprises to avoid unauthorized people to loot the sensitive data about business. In SSIS there are many vital information we are dealing everyday data loading. First of all, we need to protect the package execution and package property details from outside the team.
Following are the content of packages we use in smooth execution of packages:
- Package sensitive data such as password, connection string, etc.
- Packages and package configurations stored in SQL Server.
- Package related files such as Logs, checkpoint files, logs, package configuration XML file, etc.
- At last, Integration Services service.
ProtectionLevel property in the package is used to secure unauthorized access to packages.
Sensitive data in SSIS
Integration Services identifies properties as sensitive if those properties contain information, such as a password or a connection string, or if those properties correspond to variables or task-generated XML nodes.
In other case, it can be defined by the developer of the SSIS custom component or build-in component.
Package ProtectionLevel Property values:
In this example, I will be showing how to protect a package with password. It helps the development team to open the package with correct password.
Step 1: Create a SSIS project and add a package.
Step 2: Change the package property ProtectionLevel under security category to EncryptAllwithPassword.
Step 3: Now, save and close the package.
Step 4: Open the package under the SSIS packages node in solution explorer. For instance, Right click on the Package4.dtsx and click open.
Step 5: Provide the correct password to open the package.
Step 6: Package will be available for development or other activities now.
Other values in ProtectionLevel property
DontSaveSensitive :- Sensitive information is not saved in the package. The sensitive information is removed and replaced with blanks.
EncryptSensitiveWithUserKey :- Encrypts the entire package by using keys based on the current user. Only the same user using the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks. DPAPI is used for this encryption.
EncryptSensitiveWithPassword :- Encrypts only sensitive information contained in the package by using a password. DPAPI is used for this encryption.
EncryptAllWithPassword :- Encrypts the entire package by using a password.
EncryptAllWithUserKey :- Encrypts the entire package by using keys based on the user profile. Only the same user using the same profile can load the package.
ServerStorage :- Encrypts the package within a SQL Server msdb database. This option is supported only when a package is saved to SQL Server. It is not supported when a package is saved to the File System. The access control of who can decrypt the package is controlled by SQL Server database roles.
Thanks for reading.