Many questions on the SSIS forum relate to security aspects of using SSIS and a large percentage of those relate to the storing of passwords in configurations.
Many people have correctly observed that, whilst SSIS will generate configuration files for you, it will not include passwords, instead leaving them blank. The reason is simple, storing passwords in an unencrypted text file is a security risk and Microsoft want to make sure it is YOU that is responsible for exposing this security risk, not them.
Still though, if you enter passwords into a configuration file then you are creating a security risk. What do you do about this? Well, until Microsoft introduce encrypted configuration files (which I hope they do soon) there are a number of things you can do:
- Restrict access to the folder that the config file is being stored in.
- Store your configurations in SQL Server rather than in text files. You can restrict access to the table in which the configurations are stored and, moreover, you can encrypt them too. Voila - encrypted passwords in configurations.
- Encrypt passwords within the package by password protecting the package by setting ProtectionLevel=EncryptSensitiveWithPassword. This will require you to pass a password using dtexec.exe at execution time and here again is a security risk - if you are executing your packages on a schedule then you have to make sure that the password is not visible in your scheduling tool.
- Encrypt passwords within the package by password protecting the package by setting ProtectionLevel=EncryptSensitiveWithUserKey. I generally recommend against doing this because it means no-one else can edit the package and thus you will undoubtedly encounter problems when deploying your packages to different environments
I personally always go with option #1. It works, and I think its pretty easy to achieve.
-Jamie