Tuesday 12 May 2015

Package security in SSIS

In SSIS, Package security is possible by using password protection. It helps us to prevent from
• Unauthorized Deployment
• Unauthorized manipulation to the packages
&
In BIDS (Business Intelligence Development Studio) having better security, along with password you choose one of the property “Protection Level” also. This property tells SSIS how to handle sensitive information stored within your packages. [Sensitive information means package connection strings, user defined variables, enumerators etc.]

The options are available in protection level are explained as follows.

• DontSaveSensitive
When the package is saved, sensitive values will be removed. This will result in passwords needing to be supplied to the package, through a configuration file or by the user.

• EncryptSensitiveWithUserKey
This will encrypt all sensitive data on the package with a key based on the current user profile. This sensitive data can only be opened by the user that saved it. It another user opens the package, all sensitive information will be replaced with blanks. This is often a problem when a package is sent to another user to work on.

• EncryptSensitiveWithPassword
Sensitive data will be saved in the package and encrypted with a supplied password. Every time the package is opened in the designer, you will need to supply the password in order to retrieve the sensitive information. If you cancel the password prompt, you will be able to open the package but all sensitive data will be replaced with blanks. This works well if a package will be edited by multiple users.

• EncryptAllWithPassword
This works the same as EncryptSensitiveWithPassword except that the whole package will be encrypted with the supplied password. When opening the package in the designer, you will need to specify the password or you won’t be able to view any part of the package.

• EncryptAllWithUserKey
This works the same as EncryptSensitiveWithUserKey except that the whole package will be encrypted. Only the user that created the package will be allowed to open the package.

• ServerStorage
This option will use SQL Server database roles to encrypt information. This will only work if the package is saved to an SSIS server for execution.

To set or change the protection level of a package
Steps:
1. In Business Intelligence Development Studio, open the solution that contains the Integration Services project.
2. Click anywhere in the design area of the Control flow tab, its shows package properties.

3. Select “Encrypt all with password” option.
4. And Choose PackagePassword property.

5. Build the Solution.

After finish the building process to test the package in either ways
• By opening the solution again
• By deploying the manifest file

In the above two situations it asks password to farther changes in package of opening Sample1 Project as follows. 

Let me know what you think about this article.

3 comments:

  1. can you please tell me

    what are the issues , errors you see while migrating ssis package and how you resolved

    Data cleansing in ssis ?

    thank you

    ReplyDelete
  2. nice articles keep it up

    ReplyDelete
  3. Very nice article,Keep Updating more posts with us.
    Thank you..
    MSBI Online Training India

    ReplyDelete