SSIS Config file Encryption

Question

SSIS Config file Encryption

SMNayak

Ten Centuries

Points: 1214
More actions
January 10, 2011 at 3:13 pm

#230273

Hi,
Usually an XML config file is used for storing environmental settings (like development, test and Prod). Most of the times, one of the settings in the configuration file is connection string. Is there any way to encrypt the sensitive data in this file?
[.NET provides an option of saving the config file encrypted (using ASPNET REGIIS) but no need of a separate code for decryption. .NET reads the settings as if it is not encrypted. ]
There are options of using SQL Config for this purpose. But, as my requirement is to use a XML config, is there any option for easy encryption?
Regards,
Sandeep.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Since the XML file must be ready to be read when the package opens, I think it is hard to apply encryption to the XML file itself. What are you exactly trying to encrypt? Are usernames and passwords included in the XML config file?

What is the protection level of your package? Are you using windows or sql authentication?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SMNayak Ten Centuries Points: 1214 More actions · Answer 2

Hi,

Few examples for sensitive data:

1. Database connection string (including user name/pw)

2. Server credentials for SMTP/ FTP etc

Ideally, we will be using windows authentication for database connection string; but not all cases.

So there is necessity of storing sensitive data in the Config file.

I am using "EncryptSensitiveWithPassword" but, this doesn't encrypt config file, I believe.

Thanks,

Sandeep

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

You are right - "EncryptSensitiveWithPassword" will encrypt the sensitive items within the SSIS package itself and does not effect external confi items such as the passwords etc in the XML config file.

You will need to protect the file (NTFS permissions) or perhaps store the necessary info somewhere else (e.g. in a config database) and apply appropriate permissions to that.

SMNayak Ten Centuries Points: 1214 More actions · Answer 4

Yes...

For now, these are the only 2 work arounds i see.

Thanks

Sandeep