How to secure sensitive data for SSIS packages after deployment?

We have a SQL Server 2014 with SSIS, three software department of our organization needs to deploy their ssis packages to the same server and each department is responsible for their sensitive data and not allowed to share passwords which are included in packages with other department, and also they are not allowed to share them with SQL Administrator or SSIS_admin.

how we can manage to protect sensitive data in this scenario?

Solution

Deoending on your exact requirements you should be able to manage this by using folders and environments.

First of all start by creating a folder for each department in SSIS, then deploy each SSIS package onto the different folders. Foreach project create an environment variables. You can mark some of the environment variables as containing sensitive data, which means that the data cannot be removed.

Department A
- Projects
  - SSIS Package 1
- Environments
  - Environment 1
Department B
- Projects
  - SSIS Package 2
- Environments
  - Environment 2

This creates the necessary structure so that you can then setup different permissions for different users.

You can follow these guides to help you with setting this up: