none
Dynamically Passing Passwords to Connection Manager RRS feed

  • Question

  • Hi,

    I am trying to figure out a way to easily pass in passwords to the connection manager.  These are normal OleDb connections that connect to a database on our mainframe.  We just need a way to be able to pass in a password from some kind of source (text file or database) which is common for all the packages.  Passwords change every 90 days or so, and therefore, it'd be a lot easier to change one file, as opposed to all the packages.  I have tried the solution in the link below, but to no avail.


    http://toddchitt.wordpress.com/2008/06/27/ssis_config/

    Monday, December 13, 2010 3:22 PM

Answers

  • Couple of quick questions:

    On the subject of re-usability, is every Connection Manager that access the same resource (Data Source and Provider) NAMED the same? Because the NAME of the Connection Manager dictates the "Packag Path" property in the config. And if you have a COnfig entry for an item that it cannot resolve the path for, you will get errors and warnings.

    Second: Have you tried disabling the Configurations (simple check box in the upper left corner of the dialog box) and running the package? Does it still hang on pre-execute?

    Are you getting any warnings or errors in the Output Window? If so, could you post them back here?

    Looks like an AS 400 connection. Wouldn't you want to specify an Initial Catalog clause in the Connection String?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, December 13, 2010 7:31 PM

All replies

  • John:

    I am the author of the blog you mentioned. Sorry to hear you are having trouble with it. Can you tell me exactly what is not working for you? I have used this methodology for about 5 years now and have not had issues with it.

    Have you gone back into the SSIS Configuration database and manually added the passwords?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, December 13, 2010 3:38 PM
  • Hi John,

    It might be the easiest way to setup an XML configuration file.
    You have to enable "package configurations" in all of your SSIS packages, and set up a new XML file as configuration file, and set the connection strings for its connection managers in this XML file. You can do it using a wizard. Just right-click on your packages, and select "Package Configurations".

    Then whenever your passwords change, you'll only have to change the password in your XML file.

    (If you would like to have a bit more security, you can implement it as a database table as well.)

    Kind regards,
    Zoli
    -- Zoltán Horváth
    -- MCITP SQL Server Business Intelligence Developer 2005, 2008
    -- Please mark posts as answered or helpful where appropriate.
    Monday, December 13, 2010 3:45 PM
  • Yes I have added the passwords.

    Configuration Filter:

    Connection String for TSYIPD0D

    Configured Values:

    Data Source=TSYIPD0D;User ID=acctxa;Password=THISISWHERETHEPASSIS;Provider=IBMDADB2.1;Persist Security Info=True;

    Package Path

    \Package.Connections[SourceConnectionOLEDB].Properties[ConnectionString]

    ConfiguredValue

    String

     

    It seems to be getting through the validation phase, but when it gets to the pre-execute phase, it just stops.

    Monday, December 13, 2010 5:07 PM
  • Couple of quick questions:

    On the subject of re-usability, is every Connection Manager that access the same resource (Data Source and Provider) NAMED the same? Because the NAME of the Connection Manager dictates the "Packag Path" property in the config. And if you have a COnfig entry for an item that it cannot resolve the path for, you will get errors and warnings.

    Second: Have you tried disabling the Configurations (simple check box in the upper left corner of the dialog box) and running the package? Does it still hang on pre-execute?

    Are you getting any warnings or errors in the Output Window? If so, could you post them back here?

    Looks like an AS 400 connection. Wouldn't you want to specify an Initial Catalog clause in the Connection String?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, December 13, 2010 7:31 PM
  • I apologize if I was wasting anyone's time, but I think I have figured it out.  Todd there was nothing wrong with your instructions.  It just takes a while for the package to run fully.  I am now testing this on other packages to see if it works.  At first I was only testing it on one package to see if it was working or not.
    Monday, December 13, 2010 9:49 PM
  • :)

    Well - maybe you use lookup transformations in your packages? Lookups' cache is populated during the preexecute phase... When you run the package in debug mode (in BIDS), you can follow this population in the (Debug) Output window. Maybe you don't use it, and I'm not right, but it used to be something seeming to be a performance problem (however it's just the normal behavior of the lookup, caching before the package execution starts).

    Regards,
    Zoli
    -- Zoltán Horváth
    -- MCITP SQL Server Business Intelligence Developer 2005, 2008
    -- Please mark posts as answered or helpful where appropriate.
    Monday, December 13, 2010 10:11 PM
  • Todd,

    Also, let me just say, your suggestion about disabling the Configurations and running the package also helped with another problem.  At first, it wasn't writing any rows to the DB because the query had been written to return only certain rows.

    Thanks guys for all your help!

    Monday, December 13, 2010 10:11 PM