Intro:
To protect the data in an Integration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data. Also, the protection level that you use for a package is not necessarily static, but changes throughout the life cycle of the package. You often set one protection level during development and another as soon as you deploy the package.
Sensitive Information
1. Connection String
2. Variable values
3. The task-generated XML nodes that are tagged as sensitive. The tagging of XML nodes is controlled by Integration Services and cannot by changed by users
Whether Integration Services considers a property sensitive depends on whether the developer of the Integration Services component, such as a connection manager or task, has designated the property as sensitive. Users cannot add properties to, nor can they remove properties from, the list of properties that are considered sensitive
Totally Six modes available
| Protection level | Description |
| Do not save sensitive (DontSaveSensitive) | Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information. When used with the dtutil utility (dtutil.exe), this protection level corresponds to the value of 0. |
| Encrypt all with password (EncryptAllWithPassword) | Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package. When used with the dtutil utility, this protection level corresponds to the value of 3. |
| Encrypt all with user key (EncryptAllWithUserKey) | Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility. When used with the dtutil utility, this protection level corresponds to the value of 4. For protection levels that use a user key, Integration Services uses DPAPI standards. For more information about DPAPI, see the MSDN Library at http://msdn.microsoft.com/library. |
| Encrypt sensitive with password (EncryptSensitiveWithPassword) | Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails. For more information about passwords and command line execution, see dtexec Utility (SSIS Tool). When used with the dtutil utility, this protection level corresponds to the value of 2. |
| Encrypt sensitive with user key (EncryptSensitiveWithUserKey) | Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption. When used with the dtutil utility, this protection level corresponds to the value of 1. For protection levels that use a user key, Integration Services uses DPAPI standards. For more information about DPAPI, see the MSDN Library at http://msdn.microsoft.com/library. |
| Rely on server storage for encryption (ServerStorage) | Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio. Roles Available using this mode are db_ssisadmin – All the options- Read and Write options db_ssisltduser – View , Execute and Export own Packages , Import, Delete and Change own Packages db_ssisoperator -> Read, Execute, Export all packages. No Write option. |
Changing the Protection Level based on Package Life Cycle.
You set the protection level of a SQL Server Integration Services package when you first develop it in Business Intelligence Development Studio. Later, when the package is deployed, imported or exported from Integration Services in SQL Server Management Studio, or copied from Business Intelligence Development Studio to SQL Server, the SSIS Package Store, or the file system, you can update the package protection level. For example, if you create and save packages on your computer with one of the user key protection level options, you likely would want to change the protection level when you give the package to other users; otherwise they cannot open the package.
Typically, you change the protection level as listed in the following steps:
- During development, leave the protection level of packages set to the default value, EncryptSensitiveWithUserKey. This setting helps ensure that only the developer sees sensitive values in the package. Or, you can consider using EncryptAllWithUserKey, or DontSaveSensitive.
- When it is time to deploy the packages, you have to change the protection level to one that does not depend on the developer's user key. Therefore you typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team in the production environment.
- After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. Or, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey or EncryptAllWithUserKey, and using the local credentials of the account that will run the packages.
| S.#. | Protection Level | Pros | Cons |
| 1 | EncryptSensitiveDataWithUserKey | 1. Default. 2. Helps in Development environment. | 1. Can’t be used by other users.’ 2. Failes when moved to Prod and executed by a system account in Prod. Initial Development is easy , but downtrend we face issue. |
| 2 | EncryptAllWithUserKey | Same as above | Same as above with one level up. No user can open the package. |
| 3 | EncryptSensitiveDataWithPassword | Better Security and usage. | Every one needs to know the package password |
| 4 | EncryptAllWithPassword | Same as above with one level up | Same as above with one level up |
| 5 | Don’t SaveSensitive | It protects the sensitive data by simply not saving it in the package file. What’s not to like? No password, no user keys, everyone can open the file without error. | The difficulty, of course, comes in when you actually want to execute the package. The package can’t run if it doesn’t have a valid connection string to connect to that legacy database server from which it extracts data, now can it? So where does the sensitive data get stored, if not in the package? The answer is that we, the package developer, need to store that sensitive data in package configurations. This could be XML config files, it could be a SQL Server database, or the Windows Registry – there are many options. And each one of them can be secured in its own way, through database permissions, NTFS permissions or Registry ACLs. |
| 6 | ServerStorage | When Package is deployed to Sql server. | ServerStorage can only be used when deploying packages into a SQL Server database[12]. If you deploy to SQL, then you can rely on the database to protect your sensitive data. But as you probably know, this doesn’t apply to package development. Visual Studio can only work with DTSX files on the file system – it cannot open packages from or save packages to SQL Server. This means that while ServerStorage is a viable option for once package development is complete, but not during development. During development you want to use DontSaveSensitive, even if you are planning to deploy to SQL. |
Visit the sites
