Home > Security, SSIS > SSIS Security (Access control) – Part 3

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

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:


The default property is EncryptSensitiveWithUserKey. We can change this property at any time during Development Life Cycle.

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.

Implementation

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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: