Wednesday, July 27, 2011

Securing SSIS Packages in the File System

One of the common concern we are facing is how to secure data and the design of the SSIS ETL packages deployed in the file system. Because if there are no security restrictions, users can open the SSIS packages and if they required do the enhancement for those. Also they can get some sensitive information's stored in those packages.

To secure SSIS packages, we can use package's ProtectionLevel property. What that property does is encrypts the package definition information in the relevant XML file.
We have to set this property while we are editing the package in the BIDS.

There are different protection level options that we can use according to our security requirement.
  • DontSaveSensitive
    - Sensitive information like connection passwords are not saved in the package
  • EncryptAllWithPassword
    -Entire package is encrypted with a password that is set in the PackagePassword property
  • EncryptAllWithUserKey
    -Entire package is encrypted based on the current user and computer
  • EncryptSensitiveWithPassword
    -Sensitive information in the package is encrypted based on a package password, which is set in the PackagePassword property
  • EncryptSensitiveWithUserKey
    -Sensitive information like connection passwords in the package are encrypted based on the current user and computer
There is another protection level option as ServerStorage, which can be used only when a package is stored in a SQL Server database

No comments:

Post a Comment