Saving Password in SSIS Configurations

Question

Post reply

Saving Password in SSIS Configurations

greeshatu

Right there with Babe

Points: 791
More actions
May 19, 2014 at 3:38 am

#308184

Hi
I am using Environment variable and SQL server configuration for storing connecting string of MSSqlserver in the format
Data Source=xxxxxxxx;User ID=;Password=[PWD];Initial Catalog=[DATABASE];Provider=SQLNCLI11.1;Auto Translate=False;
and Password Protection Level : EncryptSensitiveWithUserKey
When I open the package "Connection Not found Error thrown by Connection Collection when specific connection is not found"
if I open the connection and give password .Its working fine.
Please let me know how to store password permanently in configurations

Viewing 8 posts - 1 through 7 (of 7 total)

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

I actually ran into this issue myself. I never found a resolution to it.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

What if you configure the password property itself with a configuration?

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

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

greeshatu (5/19/2014)
Please let me know how to store password permanently in configurations

I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Jeff Moden (5/20/2014)
greeshatu (5/19/2014)
Please let me know how to store password permanently in configurations
I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

And what about Oracle connections or DB2/AS400?

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

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 5

Koen Verbeeck (5/20/2014)
Jeff Moden (5/20/2014)
greeshatu (5/19/2014)
Please let me know how to store password permanently in configurations
I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.
And what about Oracle connections or DB2/AS400?

Would you store such passwords in a plain text connection? I sure wouldn't. That's a clear violation of every security protocol except the one labeled "no security". I honestly don't know how to secure such connection information in SSIS (I avoid SSIS whenever possible for such things) but would imagine there would be a way. If the connection block obfuscates and encrypts the password, then no problem. Just remember to change the password on the remote system and in the package once every 3 months (or have used something like a double GUID for the PW ;-)) and you're probably golden for audits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Jeff Moden (5/21/2014)
Koen Verbeeck (5/20/2014)
Jeff Moden (5/20/2014)
greeshatu (5/19/2014)
Please let me know how to store password permanently in configurations
I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.
And what about Oracle connections or DB2/AS400?
Would you store such passwords in a plain text connection? I sure wouldn't. That's a clear violation of every security protocol except the one labeled "no security". I honestly don't know how to secure such connection information in SSIS (I avoid SSIS whenever possible for such things) but would imagine there would be a way. If the connection block obfuscates and encrypts the password, then no problem. Just remember to change the password on the remote system and in the package once every 3 months (or have used something like a double GUID for the PW ;-)) and you're probably golden for audits.

I would store such passwords in a SQL Server table configuration and make sure that table is properly secured.

Another option is to type it in in the connection manager and then encrypt the package by setting the protection level to EncryptAllWithPassword.

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 7

don't beat us up too badly... this is the way Microsoft created it..... It may be in plain text, but, you need 'sa' privledges to open up the job and to look at it so it is secure in that way.