By default, when you create a new SSIS package in BIDS or SSDT, its sensitive data is encrypted using the Data Protection API (DPAPI). This utilizes a “guaranteed unique” key that was created when Windows was installed per user login. It is used to encrypt data on the machine by apps the use the DPAPI.

Note that if you ever want the SSIS package to run on different machines (staging, production), you must change this security setting to one that uses a password instead. Otherwise the sensitive data in the package will be unreadable.

What is considered sensitive data? It depends on the version of SQL Server you are using! By default the password part of a connection string is considered sensitive. As well, Microsoft deemed that some of the packages variables are sensitive. You don’t get to change that setting. Some task-generated XML nodes are also marked sensitive. Again, you cannot control which nodes are considered sensitive.

watch1

Note that with SQL 2012 and newer, you can mark parameters as sensitive. These parameters work like variables but can be used at the SSIS package level or the project level.

watch2

As well, when assigning values to these parameters in the SSIS catalog via an Environment setting, you can choose whether the values assigned to them are sensitive.

watch3

When ready to check your SSIS package into a Source Control Manager (SCM) such as TFS, change the package ProtectionLevel property to EncryptSensitiveWithPassword or EncryptAllWithPassword. That way, other developers (including future developers) can load and modify the package. As well, the password can be used to execute the packages.

Note that if your SSIS package will be deployed to MSDB (used in SSIS 2008 R2 and older back when the SSIS catalog didn’t exist), an additional option will appear – ServerStorage. It should only be used with the MSDB database deployment method.

I had a student tell me about an SSIS developer they had just let go who had several hundred packages created on her machine. All were checked into source control but the ProtectionLevel was left at EncryptSensitiveWithUserKey. An ambitious IT team immediately wiped her machine clean with her login profile after she left, leaving the Dev team stranded with having to recreate all the sensitive parts of the packages – ouch!

Remember – by default, all newly created SSIS packages are set to EncryptSensitiveWithUserKey by default!

You can learn more about the security settings at the following site. Be sure to select the correct version of SQL Server on the page for the most accurate information for your servers.

https://msdn.microsoft.com/en-us/library/ms141747(v=sql.130).aspx

Best wishes and happy ETL-ing!