SSIS Connection Manager passwords
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?
March 12th, 2007 5:51pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2007 5:53pm

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'."
March 15th, 2007 8:53pm

Try the SQL Server Service Pack 1.http://www.microsoft.com/sql/sp1.mspx
Free Windows Admin Tool Kit Click here and download it now
March 15th, 2007 9:03pm

Already tried that, as well as SQL SP2. No luck.
March 15th, 2007 10:46pm

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
Free Windows Admin Tool Kit Click here and download it now
March 15th, 2007 11:06pm

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".
March 15th, 2007 11:06pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 15th, 2007 11:29pm

Did you set a password on the package?Try using "EncryptSensitiveWithUserKey" and see if it works.
March 15th, 2007 11:38pm

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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2007 10:43pm

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?
May 18th, 2007 3:09am

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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2007 5:39am

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.
May 21st, 2007 9:04pm

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.
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2007 11:08pm

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.
October 11th, 2007 11:12pm

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?
Free Windows Admin Tool Kit Click here and download it now
October 16th, 2007 6:47pm

"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.
October 17th, 2007 4:37pm

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
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2008 10:54am

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 "Somewhat be resolved thou"? I tried it with no luck with using EncryptAllWithPassword or EncryptSensitiveWithPassword I'm running on VS 2005 SP1. If you are running on VS, it is using the SSIS Package configuration files?
December 3rd, 2009 9:15pm

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.
Free Windows Admin Tool Kit Click here and download it now
December 24th, 2009 3:30am

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
April 13th, 2011 10:15pm

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.
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2011 4:50pm

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.
April 20th, 2011 11:49pm

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

Other recent topics Other recent topics