CODEWITHSUNDEEP
sql-serveroracle-databasessisdatabase-connectionconnection-string
techspider
techspider

Reputation: 3408

Storing Password in SSIS Package

I have an SSIS Package which is used to connect Oracle. I used the Oracle Provider for OLEDB connection manager in SSIS. I have specified my server name, user name and password, selected the check box "Allow Saving Password". It is running fine on my machine but when I give a copy to another developer, he fails to run as he gets null password error. Is there a way to save password in SSIS Package so that we don't need to worry about that?

Upvotes: 3

Views: 12571

Answers (2)

Dave C
Dave C

Reputation: 7402

You can utilize XML configuration files to store the passwords, but since they are stored in clear text, additional safety measures would be advised to protect the files from prying eyes.

This MSDN blog illustrates how to add and utilize configuration files very nicely.

Upvotes: 0

Thomas Tschernich
Thomas Tschernich

Reputation: 1282

Your goal is the Package Protection Level. By default, it is set to EncryptSensitiveWithUserKey. This means, that all passwords are encrypted using the authors Windows Account. As soon as another user tries to load the package, the passwords will be lost.

There are some other options for this package property. One option would be to use EncryptSensitiveWithPassword or EncryptAllWithPassword. Using these options, anyone can open an run the package as long as he/she has the password.

Yet another option would be to use the option DontSaveSensitive. You will then use the Expressions of the Oracle Connectionmanager to overwrite either the password or the full connection string. I can confirm that this is working pretty fine, we are using this method for our Oracle passwords for years. As this option results in having the password saved on your system(s) unencrypted, you need to be sure to secure it in some other way.

Further reading: https://msdn.microsoft.com/en-us/library/ms141747.aspx

Upvotes: 6

Related Questions