Connection passwords with and without SSIS Package Configurations - password not sticking
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database. I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again. This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails. What do I have to do to make that password actually stick!? Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio. Here are at least some of the version information, which may or may not be useful. Microsoft Visual Studio 2005Version 8.0.50727.42 Microsoft .NET FrameworkVersion 2.0.50727 Installed Edition: Professional Microsoft SQL Server Analysis Services Designer Version 9.00.3042.00 Microsoft SQL Server Integration Services DesignerVersion 9.00.3042.00 Microsoft SQL Server Reporting Services Designers Version 9.00.3042.00 SQL Prompt 3.5 Thank you for any help you can provide. -thursday's geek
May 8th, 2008 11:33pm

So, You want to save the password in the package itself and not in config file. The recommendable way is, set the ProtectionLevel Property of the package to EncryptSensitiveWithPassword. So that it can protect those data using the password you enter. And,I Won't recommend this practice. There are some problems when using this apart from configurations. 1. You can't update password in future (Without redeploy the package) 2. Every time you open /Debug / Deploy the package you have to type the password for each of the packages (How many packages you have now to move to production? ). Just try this for single package, You may feel the pain. Configuration file is elegant when compared to this. When your configuration file option fails, try to find out where you missed (I am sure its your fault and you miss something), All of a sudden don't jump to otherirrelevant solutions.
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2008 12:38am

I'm also sure it's my fault and I'm missing something. I just don't know what the something is. Ok, I can see the benefit to using a configuration file for the password, so that changing the password can more easily be handled.But when I look at the configuration file, it looks like it isn't saving or storing the password. For instance, here are two lines from the configuration file, one with password, one with login name. (I would imagine that encrypting this file would be useful, especially if it did save the password.) - <Configuration ConfiguredType="Property" Path="\Package.Connections[DB_HGSC].Properties[Password]" ValueType="String"> <ConfiguredValue /> </Configuration> - <Configuration ConfiguredType="Property" Path="\Package.Connections[DB_HGSC].Properties[UserName]" ValueType="String"> <ConfiguredValue>hgsc_admin</ConfiguredValue> </Configuration> There isn't a value for the password. And, of course, I get an error because there is no password. But when I try to save it in the connection manager editor, it won't save. When I try to execute the package, I get the following error: Error: 0xC0202009 at MAPOPTIX_ehairasp, Connection manager "DB_HGSC": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'hgsc_admin'.". And, of course, when I go back to the connection manager editor, it still doesn't show the password, nor will it connect until I type the password in AGAIN (and quit using the configuration file). Again, thank you for any help you can provide. -thursday's geek
May 9th, 2008 1:08am

The Password property is marked as Sensitive in SSIS - that means it will not be saved to a configuration file. Instead, save the ConnectionString property to the configuration file, then edit the configuration file in Notepad, and add the password back into the connection string. I use this method regularly, and it works well.
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2008 3:31am

Agreed, this is the option with the least amount of headaches. Remember, however, if you switch your package configurations around using the wizard that you will need to add the password back in. Also, I think that the dontsavesensitive is the best encryption method for this type of activity...
May 9th, 2008 3:36pm

I did this but when I run the package through SQL Server agent it gives the following error: ...Password length = 0... Thanks
Free Windows Admin Tool Kit Click here and download it now
May 13th, 2008 6:03pm

Post the complete error message, please.
May 14th, 2008 12:38am

Hi, I have the same problems. And actually I write by myself the password in the dtsconfig file. But, each time that i build the package the dtsconfig file rewrite the file and my package fail. I use the option encriptAllWithPassword but i can see that i can edit de dtsconfig without problem. I hope that you can help me! Julin Castiblanco P. Bogot, Colombia.
Free Windows Admin Tool Kit Click here and download it now
May 19th, 2008 10:27pm

Edit the .dtsconfig in the original location, not in the BIN folder, when you edit it. Building the package does not recreate the dtsconfig, it simply copies it to the bin folder.
May 20th, 2008 3:44am

You have all the reason. Thank you jwelch
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2008 4:02am

This might be helpful. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c720e694-2f58-483a-9cd7-3feb7de2db7b/?prof=requiredGISEngineer
April 13th, 2011 10:20pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics