SSIS: How to store connection string password in configuration?
Axel Dahmen
I've been trying to store a connection string in any kind of configuration. It seems SSIS never stores the password there.
Do I really have to always enter the password manually into the configuration value? Isn't there an option to have it persisted somehow? Why does the option "encrypt sensitive data" exist if there isn't any sensitive data to encrypt?
Your help is appreciated.
TIA,
Axel Dahmen
Todd C
SSIS will not automatically save the Password from a Connection String
property in a Configuration. The thing to do is first create the
Configuration entry (xml, SQL Server, etc) then go and manually edit it to
include the "Password=abc123;" clause. But once set up, you should never have
to touch it again.
Check out this blog: http://toddchitt.wordpress.com/2008/06/30/ssis_pw/ for
tips on how to set up a Configuration Database
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]
Scho
It would also be key to add that although that's a bit more secure
there is a slightly different way mentioned in the link.
The lazy-man's method of doing this is to use a configuration file and
then simply manually edit this and enter the password in the
connection string and password attribute boxes. If you then make the
config read only; it should refer to this and keep your password
saved. The only problem is anyone can see the config and can get your
username and password.
If you've read that and don't know how to make a config file or what
exactly it does. Essentially when the package runs it can refer to the
file and not the default settings you've got in the package. To make
one; in the control flow tab/screen don't have anything selected;
select SSIS from the menu at the top and click 'Package
Configurations'.
Enable this feature and then click 'Add'; the rest should be step by
step and you'll be able to follow.
As a note; if you change any part of the package settings (IPs within
connection managers etc) then you will no doubt need to re-generate
the config file. I haven't tried this with non-read only tho as my
passwords kept disappearing from there too when the option wasn't
turned on the file.
Hope that helps.
JB
Todd C
It took several iterations to finally settle on the methodology I published
in the blog. The main reason I didn't go with one big (XML) Config file with
multiple settings was because if the file has a reference to an object (like
a Connection String) and that object is not in the package, you will get
errors. With the SQL Configuration table, you can store multiple 'Filters' in
one table and simply pick and choose which you need in your package.
If you are worried about security, you can lock down the SQL Config Database
in the production environment to Developers (so they can't read passwords)
and open it only to the SQL Agent account.
Also, I'm more of a SQL guy than an XML data guy and, well, to a hammer,
everything looks like a nail!
--
Todd C
Axel Dahmen
> But once set up, you should never have
> to touch it again.
Unfortunately that's not true. When the config is updated for any reasons the password becomes deleted again.
Of course, as you've mentioned, if one is to set up a separate configuration file/filter for each connection then this would work though. Still I'm not sure if this is something worth to be desired.
I've created a suggestion at MS Connect (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361732) to apply encryption to external configuration stores as well, hence providing for storing passwords in connection strings within these configuration stores.
Looking forward to get your vote... ;)
Best regards,
Axel Dahmen
---------------------
"Todd C" <To...@discussions.microsoft.com> schrieb im Newsbeitrag news:92293F73-E0A5-4982...@microsoft.com...
Scho
Can see why you've come to that conclusion and does seem the best way
to implement it by the looks of things so I may use that method in the
future.
SSIS though is a fickle thing and for some strange reason (that still
bemuses me) is that is seemed to lose the connection string every 5
minutes during one day with me until I figured out about saving them
in the config file and then setting it as read only. This was only
during test runs in BIDS though as I never got as far as a roll-out to
a DB instance at that point.
Axel - the bit about not updating is not when using BIDs to execute
the package - build and deploy it to a SQL instance and when setting
it to run you manually edit the connection strings in there. That's
the method Todd suggested; and when you think of it the most secure
unless you hand out your sa details to everyone!
All good eitherway; 2 more ways around the mess that is SSIS. As a
note; I'm a SQL guy as well - and BIDS is pretty much a bent nail in
terms of implementing SQL if there ever was one; especially after I've
finished with it! :o)
Hope that helps you though Axel.
Scho
p.s.
Todd - nice blog I've saved that for later ;o)