April 11, 2006 at 9:07 am
Hey all, more mysteries
Saved passwords are being removed behind the scene in the connections and config file.
The SSIS application I'm building uses multiple packages chained in parent child calls for reuse of common functions. Currently the all use the same two database connections and the same dtsconfig file.
Each time I close and then reopen the project (and some times when I've been working on it and added a new package) the DB login passwords are gone from the connection managers even though the save password box is checked and the passwords are also deleted from the config file.
I have to keep going into the xml file and connection managers and putting the password back in.
How do you prevent this?
Thanks everyone, this is a lot to learn under pressure.
Skål - jh
April 11, 2006 at 5:41 pm
Security. I think that Jamie Thompson blogged about this when he was talking about whether to use encryption when saving to a file and similar things. Saving without encryption means that the values are not saved. Using Trusted connections, you don't have the problem that I'm aware of.
Check out Jamie's blog for some more details on this:
http://blogs.conchango.com/jamiethomson
April 13, 2006 at 11:49 am
Ah, well I cannot encrypt the package or put a password on the package. I simply need the DB connections to keep the passwords and use the ones in the dtsconfig file.
Searching Jamies blog did not find any entries for 'password' but I did find this other mention of what you are talking about but the passwords it is refering to are package passwords and I need to persist the login passwords for the connection managers. http://www.databasejournal.com/features/mssql/article.php/10894_3580216_2
Thank you so much, I still don't understand why it keeps deleting them from the config file. Down stream admins will be deploying these packages and I cannot lock them out with the features mentioned above.
much appreciated!
Skål - jh
April 13, 2006 at 12:14 pm
What about storing those in some sort of external file and using an expression to read them in? That should get around the issue of saving the password in the package and allow the password to persist. Besides, you might even be able to re-use the file.
Make sure you set the DelayValidation to True for these expressions or they probably won't be set correctly. If they're still deleted from the file, I'm not quite sure where to go - that sounds like a bug.
As for Jamie's blog, I could have sworn that he was referring to how passwords for connections are saved within the package. I could have been thinking of a different blog site. I'm pretty sure that when you save without encryption, the passwords are not saved.
-Pete
April 13, 2006 at 12:19 pm
Yeah I think your right about the connections, they do not save the passwords. Something though is sometimes deleting them from the config too, maybe it's happening when I add a new package to the project and configure it to use the same config file as all the others.
Delay Validation is a great tip, I'll start looking to see where all I can set that.
thanks!
Skål - jh
April 14, 2006 at 7:07 am
I don't think I've got anything up there specifically related to this stuff. I thought there was something on Kirk Haselden's blog but unfortunately it seems not.
Passwords will not be saved in the package except in an encrypted form. This is determined by the ProtectionLevel property of the package. If you don't choose to store them in an encrypted form then you need to store them in a configuration.
Brian Knight has talked about it a bit: http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/19/34.aspx
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 14, 2006 at 7:17 am
Yes I've been storing them in the config and it usually works. I think I figure out what's happening with it. When I add a totally new package and set it to use the configuration file all the packages in the project use the new one actually overwrites it and removes the passwords. If I copy the package at the file system, re name it, then add it to the project and edit it as a clone then the config. file seems to remain intact.
Brian's post is good to know, but the deployment is going to be out of my hands and managed by server admins who are not dba-s or developers so I can't require encryption and package level passwords. They will only bulk copy files and binaries to prod and we're locked out from any server scripting et.al.
thanks gang!
Skål - jh
April 14, 2006 at 7:27 am
I strongly recommend you don't do that. Share .dtsconfig files betwen packages as much as possible. If you use multiple .dtsconfig files with the same information then you have to make sure they are all consistent. That's bad bad bad.
If you want to use an existing config file then its quite easy do so. When you set up the config in SSIS Designer browse to an existing file and you will be prompted with the message: "Do you want to reuse this file as the configuration file or overwrite the file with new configuration settings?". Select "Reuse Existing".
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 14, 2006 at 7:33 am
Yes I'm with you that's exactly what I've been trying to do from the beginning
I want to have one and only one single config file for all 15 or so packages in the project.
they all use the same two connection managers and passwords.
but the passwords keep getting deleted from the config file behind the scenes. I thought that was happening when I added the config file to a new package but guess it's something else stranger. I need that config file to stay as it is every package fails when the passwords are removed by the IDE somehow.
thanks man!
Skål - jh
April 14, 2006 at 8:04 am
SSIS will never store the password for you. You have to manually edit the .dtsconfig file to add the package into the connection string. [That's another point actually - you should store the ConnectionString property not the individual InitialCatalog/Username/Password/etc... properties.]
Once you have edited the file once you should never need to do it again on that environment.
If you are seeing different behaviour then something is wrong.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 14, 2006 at 10:36 am
Ah interesting.
Thanks so much for the pointer!
I can't have just one config file after all. It works if the number values stored there are the same for every package, but as soon as one has an additional one all the other packages will fail because they try and force loading of a property they do not have. Not the greatest maintance option. So I now have a bunch of config files that all hold the same connection strings but since I had to give eveyone private connection managers in hopes of stopping the hanging issue they all have to get their own config files. Lot's to remember when deploying that's for sure. thanks again for all the help.
Skål - jh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply