Package Deployment Model : Help with Connection Managers and Relationship with XML Config Files

Question

I have a Visual Studio 2017 SSIS project with 11 packages. We are using the Package Deployment Model. My connection managers connect to a third party Oracle data warehouse. My dba has told me to save the package Protection Level as 'DontSaveSensitive' for deployment. We don't deploy to Azure.

I create a connection manager, enter my credentials and it tests OK. I save the package, close the package and reopen it. My newly created connection manager goes offline (with the red down arrow) and it 'forgot' my password. BUT my UID and password are stored in an XML configuration file and 'Enable Package Configurations' is ticked.

1. I'm confused how a connection manager relates to an XML configuration file. Why does it not use my credentials from the config file when the Protection Level is set to 'DontSaveSensitive'?
2. Also if I used the default 'SaveSensitiveWithUserKey' (packages work OK under this), and I change my third party DW password (after 90 days or so), all my packages suddenly break and my account gets locked.

What should I do in development and deployment to end these issues?

Answer 1

Hi @ph ,

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.

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.

Typically, you may change the protection level as listed in the following steps mentioned in the official documentation.

access-control-for-sensitive-data-in-packages

Also check this which gives information with pictures and words.

ssis-package-protection-level

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.