SSIS Package Config File Encryption

We have SSIS package config files that contain DB encryption passwords or PGP encryption passw开发者_如何学Cords. I came to the conclusion that there is no "silver bullet" solution for encrypting SSIS package config files like with web.config files ect.

Should we consider not using config files at all for SSIS packages and if so what other options do we have available for storing settings?

Encryption of configuration files are not handled by SSIS itself. You can use NTFS encryption and/or ACLs to control access to config files and contents. It beats learning and administering a new access/encryption mechanism, and nicely ties in to your AD efforts.

Another option is to store the configurations in a SQL table and use SQL security to control access, but most administrators seem to prefer file-based management.

Could you use a table for config storage and lock down access to it? Throw that database/log/backup in an EFS protected folder and the only people that could access would be SQL sysadmins/or authorized accts and whoever has access to decrypt efs with a recovery account and restore the database (domain admins?).

You could also use SQL 2005's native encryption and write your own procedure to access the data and then set the connection properties in a script task. I haven't done this, but theoretically it might work.

While storing configuration information in a database is a viable alternative, if you are stuck with XML configuration files (for a variety of reasons), you may try BI xPress Secure Configuration Manager or SSISCipherBoy (freeware, I am affiliated to this project). These two utilities answer your question precisely.