none
Connection manager doesn't save password RRS feed

  • Question

  • Hello,

    My connection managers don't save the passwords. The definitions are read from a configuration file, but when I view the properties, I see a blank field, and then if I fill the password, close the dialog and open it again, the field is blank again, although I checked the "Save my passwords" checkbox.

    What's going on?

    Thursday, October 14, 2010 1:25 PM

Answers

  • Yes. That the whole reason for Configurations! When the package runs, the Configuration settings will retrieve the Connection String from the Configuration database (or xml file) and apply it to the Connection Manager(s) before any real package work is done.

    Check out my blog on Configurations.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, October 14, 2010 2:21 PM

All replies

  • Check the package property "Protection Level" Set it to "Encrypt Sensitive With User Key"
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, October 14, 2010 1:41 PM
  • But then if I deploy the package to another computer, it won't let me work on it because it won't be the same user. I had this problem in the past.

     

    Thursday, October 14, 2010 1:56 PM
  • So, change the package protection level. Make it to "save sensitive with password" and use that password while editing the package on other machine.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, October 14, 2010 2:03 PM
  • So either he has to enter a new password for the Connectin Manager, or enter a new password everytime he opens and saves a package.

    "6 of one, half-dozen of another"

    Do you (OLAPWisdom) have multiple developers working on the same package? If so, then there's not much you can do, other than this:

    Set the shared connections in the project to use Integrated Security and give all the developers the propert rights to read the meta data of the objects they are interfacing with. Then, in your Configuration database (or XML file) configure the Connection String property of the Connection Manager(s) in question to use the Username/Password as supplied.

     


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, October 14, 2010 2:08 PM
  • No, but I need to deploy the package to the production server. Can I run it if it's protected?

     

    Thursday, October 14, 2010 2:12 PM
  • If you execute it via SQL Agent, SQL Agent Service user must decode the password. Service User could be changed via "Services" in production server. But this is not a recommended thing. SQL Agent job could also be runned as another account (aka Proxy Account) 

    So you have to create a proxy account in SQL Server to decrypt the sensitive information. (That proxy user must be the person who encrypts the package.) And make the job executed with that proxy user.

    For more information on Proxy Account Creation: http://msdn.microsoft.com/en-us/library/ms189064.aspx

    (Edit: Todd C suggested a good method. Please follow his suggestion first)


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me

     

    Thursday, October 14, 2010 2:20 PM
  • Yes. That the whole reason for Configurations! When the package runs, the Configuration settings will retrieve the Connection String from the Configuration database (or xml file) and apply it to the Connection Manager(s) before any real package work is done.

    Check out my blog on Configurations.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, October 14, 2010 2:21 PM
  • Sorry but I don't agree with Oner Omer Oztuk's advice (no disrespect as I have read many of your other responses and know you have a good depth of knowledge).

    If the Username and Password are being saved in a Configuration database table, and passed to the package via the standard functionality provided for such by SSIS, then a Proxy account in SQL Agent won't help because SSIS is still going to try to access that Connection Manager with the username and password. Only if the Connection Manager is set to use Integrated Authentication will the credentials of the Service Account (or proxy, as appropriate) come into play. Of course, if you also access shared folders on the network and the Service Account does not have access there, then yes, you'll need a Proxy that DOES have access.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Thursday, October 14, 2010 2:27 PM
  • Sorry but I don't agree with Oner Omer Oztuk's advice (no disrespect as I have read many of your other responses and know you have a good depth of knowledge).

    If the Username and Password are being saved in a Configuration database table, and passed to the package via the standard functionality provided for such by SSIS, then a Proxy account in SQL Agent won't help because SSIS is still going to try to access that Connection Manager with the username and password. Only if the Connection Manager is set to use Integrated Authentication will the credentials of the Service Account (or proxy, as appropriate) come into play. Of course, if you also access shared folders on the network and the Service Account does not have access there, then yes, you'll need a Proxy that DOES have access.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

    +1 :)
    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com/blog Follow Me
    Thursday, October 14, 2010 2:40 PM