Config and Connection String

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

That behavior is by design; BIDS will not store the password for you; you have to add it manually. Hopefully you will not need to do it very often

|||

dgrm44 wrote:

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

Nope. You've got it figured out. BIDS doesn't store passwords.|||

The other guys are quite right but it may help to explain why.

As you may or may not be aware, a few years ago Microsoft introduced a programme called the 'Trustworthy Computing Initiative'. An effort to try and make their products more secure. This is one of the main reasons it took them so long to get Vista out of the door.

Anyway, you can see the effect of this here. If you are going to store passwords in a text file (which is inherently a security risk) then Microsoft want YOU to be responsible for doing that - they will not do it for you.

-Jamie

|||

The what is the best practice for storing my ssis connection string passwords if not in the config file?

|||

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)|||

Phil Brammer wrote:

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

|||

Jamie Thomson wrote:

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

Well at present, it is the best practice as it's really the only practice that works, correct? |||YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?|||

dgrm44 wrote:

YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?

You enter the package password into that property. Then all sensitive data (passwords!) are encrypted with that password entered, using EncryptSensitiveWithPassword. Then, to open/execute the package, that password will be required to be passed into DTEXEC upon execution in order to decrypt the sensitive information. This is a good alternative.|||

More info here:

Storing Passwords

(http://blogs.conchango.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx)

-Jamie