Secure deployment of SSIS package

Question

We are in the process of completing SSIS packages. However as we move towards the deployment phase surprisingly we realized that deployment of SSIS package is not really straight forward as terms of security.

we need to run a SSIS package using either batch file or .NET program in production server. The caller (batch or .NET) should call the SSIS package by supplying parameters (such as source file, destination database, userid, password etc) and also read return code (Success/Error) from SSIS. The user id and password should not be in clear text format visible to anyone. We did some research and found that deployment can be done using many options such as XML config, SQL server Configuration, environment variables, Registry etc but did not mentioned any about password encryption. How can we achieve security and ensure flexibility deploying same package in multiple environment (DEV,UAT,PROD) and simple changing the environment specific variables at the time of deployment.

I am looking for somthing where MVC/Entity framework the connection strings are automatically encypted when the application is run first time using following code.

Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
            ConfigurationSection section = config.GetSection("connectionStrings");
            if (!section.SectionInformation.IsProtected)
            {
                section.SectionInformation.ProtectSection("DataProtectionConfigurationProvider");
                config.Save();
            }

from where will the package be executed? if remotely/on ssis server then you can make web-service which will take the params and execute package info. If user is supplying own password then you can set package protection level to not store password info — T I
Hi T..thanks for getting back. The package will be run from application server which is different from database server. A schedule program will run the package (Batch or .NET). The use id and password need to be stored in encrypted format. — UrbanPlanet

billinkc billinkc · Accepted Answer · 2013-09-17T03:48:33

Your question confuses things.

By default, SSIS is going to encrypt anything that could be of a sensitive nature by using the author's Active Directory, AD, account. SSIS Packages Using Package Protection Level This Package Protection Level is EncryptSensitiveWithUserKey If you're using a SQL Server user and password in your connection string, the at-rest value in your SSIS package is going to be encrypted. Don't believe, me, open the file and find the Connection Manager's ConnectionString property.

Deployment is not done through XML, SQL Server, Registry etc. Those refer to methods of creating and storing configuration values that an SSIS package may access during run-time. Configuration has no concept of decrypting values. That isn't to say you can't encrypt values and have them decrypted for the caller, just that there is not built in mechanism for signifying "decrypt using this key."

An approach we are using is to use SQL Server for the holding of encrypted data. We create roles authorized to use the keys and then have computed columns that automatically decrypt the data based on role membership. A person not in that role querying the column will only ever get NULL back as the decrypted value. It's working well for use. EncryptionPOC

Deployment is done through

file copy
dtutil.exe
dtsinstall.exe
.NET api

Secure deployment of SSIS package

1 Answers