A Level of Protection

Understanding how the ProtectionLevel property works will give you a level of comfort in protecting your SSIS packages.

One property of all SSIS packages that you must understand is ProtectionLevel. This property tells SSIS how to handle sensitive information stored within your packages. Most commonly, this is a password stored in a connection string.

Why is ProtectionLevel so important? If you don't set that property correctly, the package may become unusable. Other developers may be unable to open the package or the package may fail when you execute it. Understanding these options lets you get out in front of possible problems and will help you to fix an issue if a problem crops up. In a perfect world, you would not need to store sensitive data, but each and every environment is different.

Let's look at each of the ProtectionLevel options:

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 is going to 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.

So that's it. This option is pretty basic but it is important to understand so that you can be spared unnecessary frustration.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Windows Admin Center vs. Hyper-V Manager: What's Better for Managing VMs?

    Microsoft's preferred interface for Windows Server is Windows Admin Center, but can it really replace Hyper-V Manager for managing virtual machines? Brien compares the two management tools.

  • Microsoft Offers More Help on Windows Server 2008 Upgrades

    Microsoft this week published additional help resources for organizations stuck on Windows Server 2008, which fell out of support on Jan. 14.

  • Microsoft Ups Its Carbon Reduction Goals

    Microsoft on Thursday announced a corporatewide carbon reduction effort that aims to make the company "carbon negative" by 2030.

  • How To Dynamically Lock Down an Unattended Windows 10 PC

    One of the biggest security risks in any organization happens when a user walks away from their PC without logging out. Microsoft has the solution (and it's not a password-protected screensaver).

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.