SSIS Connection Manager passwords

YoungEngineer

I am having a strange problem in that when ever I create a connection to an OLE db source and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked. Even if I do a test connection at this point, it wont connect because it does not have the password.

Is this some sort of bug Is there a workaround

Phil Brammer


Seems like a bug. Though things work fine on my end... SSIS SP1.

The password box always appears empty when I come back in and test connection always works.

YoungEngineer

I downloaded Service Pack 1 for Visual Studio 2005, Version 8.0.50727.762 (SP.050727-7600), but I still get this error message. I tried the SA username and password for the connections in connection manager, but even after I checked the save password box and close the connection and reopen, it gives me the following error message:

"Test connection failed because of an error in intializing provider. Login failed for user 'sa'."

Phil Brammer

Try the SQL Server Service Pack 1.

http://www.microsoft.com/sql/sp1.mspx

YoungEngineer

Already tried that, as well as SQL SP2. No luck.

Jamie Thomson

YoungEngineer wrote:

I am having a strange problem in that when ever I create a connection to an OLE db source and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked. Even if I do a test connection at this point, it wont connect because it does not have the password. Is this some sort of bug Is there a workaround

What is the ProtectionLevel property of your package set to I'm guessing its ProtectionLevel=DontSaveSensitive

-Jamie

Tom Phillips

The interface ALWAYS blanks the password box when you reopen the connection properties. When you clicked ok after the test it saved it and should work fine.


If it didn't do that, someone who doesn't know the password could open the connection and change the database to anything, and get access to something they should not.

It is a feature and "works as designed".

YoungEngineer

Tom:

im fine with the password box being blank. the problem is that even after i set the password, and the save password box is checked and i close that connection and reopen it, and then click the Test Connection button, i get the error message above.

Jamie:

Ive tried Encrypting all sensitive with userkey and password...still same problem.

Ive also tried recreating this same scenario on three different machines, and i have the same problem.

Phil Brammer

Did you set a password on the package

Try using "EncryptSensitiveWithUserKey" and see if it works.

YoungEngineer

I have tried all sorts of password protection mechanisms...userkey, password ..every option available.

I double click the Connection Manager connection to my sql db, my username is there..type in the password, check the

"Save my password" box...and then OK...now once that window closes, when I double click the same connection...the password box is empty.

Whats going on here thanks

jwelch

I think (but I'm not positive - can't test it right now) that the box stays empty even if it did save the password. If you test the connection, is it successful

Rafael Salas

I think this is a 'by design' behavior. If I recall correcty the save password option is only good for the duration of the BIDS sesion (at least when you use DonSaveSensity). At run time there are several tchniques to get the passwords set. This KB describes 4 methods:

http://support.microsoft.com/kb/918760

YoungEngineer

I think it does save the password, but if you double click the connection in the connection manager, then it refreshes the password thereby making it blank. i wish by default it just showed the password in asterisks or soemthign even if you reenter the connection from connection manager instead of deleting it.

hechter

I didn't have this problem until I put the UserName and Password as part of the Package Configuration. As soon as I did this, the Password was never remembered anywhere. As soon as I would close the connection in Studio, the password was gone. Hence there was no way to test anything in the Studio. As soon as I removed the UserName and Password from the Package configuration, it saved them as I expected and I could run in Studio (and via the execute package utility)

The interesting part is that even when I did specify the UserName and Password in the Package configuration. I would Build the project, run the deployment and the install (where I once again would specify the password). When I would run the installed version (via the execute package utility) would NOT be able to aquire a connection either. I believe that it failed due to a password problem.

Seems like there is a problem in SSIS.

jwelch

Did you edit the configuration file directly to include the password in the connection string SSIS will not persist a password to the config file through the IDE - you must explicitly edit the config and add it.