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?

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'."

Try the SQL Server Service Pack 1.http://www.microsoft.com/sql/sp1.mspx

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

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

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".

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.

Did you set a password on the package?Try using "EncryptSensitiveWithUserKey" and see if it works.

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

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?

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

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.

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.

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.

jwelch wrote: 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. Is this true for "installed" packages also? Does the "installation" process also NOT save passwords?

"Installed" packages are just copies of the development packages - you can install by doing a file copy of the packages. The configuration behavior is the same, regardless. Depending on the package ProtectionLevel property, you may be able to save the password in the package itself. However, SSIS will still not put the password in the config file, you must edit that in yourself.

Isn't it a little bit of waste to fill this blogg with questions like "did you click the save password box"?Well anyway here comes a suggestion to your problem YoungEngineer...You are obviously using "SQL Server Authentication" instead of "Windows (Integrated) Authentication" - a perfect and normal choice according to myself. Using the sqlauthentication you get to apply a password as well. Your problem is that when you click the box to save the password it DOES NOT SAVE. - I have tested it and I have the same problem. Even if I manually add the "Password=xxx;" line into the connection-string in Properties it is automatically removed. This is a feuture and due to some stupic security settings.Suggested Resolution:This can somewhat be resolved tho by setting a password on the SSIS-package and change the SecurityProtectionLevel to use EncryptAllWithPassword or EncryptSensitiveWithPassword. Then redeploying the package.You can see some other people with the same problem -here-.Cheers,Danny of Sweden

I'm having the same problem... I enter the passwords in & click test connection & 'save password' is checked & click ok, but if I re-open it there's a blank password & if I click 'test connection' it fails.But if the password is actually still being retained & that it is a 'by design' issue, then how come the *.dtsx file run in a job fails on the first step?DELETE FROM tbl_Package_LogINSERT INTO tbl_Package_Log(StartRun) SELECT GETDATE() I have 8 jobs running a single *.dtsx file which does a data pump for every table in a source database to put it in the archive destination database. This was setup by someone else who has since left. I noticed these jobs have been failing for quite some time. Opened one & fixed many errors/cautions to do with new columns or removed or modified columns. However it still fails, no error given. So I'm assuming it's the password issue.

Same issue here with SQL Server 2008 version with the latest Service Pack. One thing I've noticed, when you open from a stand-alone package file, in the connection manager, Persist Security Info can not be saved to True, it always going back to False next time when you open up the window. But if you remove this connection obj from all activities in Control Flow and Data Flow, then it CAN be saved to True and will stay like that. By designed, Password textbox always blank out when the Connection Manager is reopened. So by now, after the connection obj being re-linked back to all activities again, I assumed that this time would work.......but it doesn't :( Therefore I create a connection manager obj with same configuration in inside the Connection Mangers panel, set Persist Security Info to True, type in password, and Save. After I linked this new Connection Manager Obj to all my activities, and save it, go back this stand-alone package file and run it. It finally worked! Another way to workaround without recreate the Connection Manager obj(s). Use note pad, put your password and user name into Configuration file (.dtsConfig) file and load it up from Configuration tab in Execute Package Utility screen. Note here, you need to tell Configuration file generate a tag field for password and user name, and then you can just simply fill out the password manually. such as <ConfiguredValue>.....</ConfiguredValue> So I guess, if you create a package file from MS SQL Server Management Studio, not SQL Server Intelligence Development Studio, by default, Persist Security Info always set to False. Hope this would still help. GISEngineer

I agree with Tom I believe what happens is that when you open the editor and enter a password, the password is immediately encrypted. Then if you open again you see the password gone. Although, I do believe the password is saved as long as you don't open the editor. And here is why: If you create the connection, save the password, and then close the editor, if you click on the connection inside the Connection Manager panel below, and look at the properties without openning the editor, you will see the password shows up. Then, if you open the editor, is gone.

be sure that login that you use to connect to Server has respective to desired action permisions ('db_owner', for instance)