Weird question in connection manager, connection manager seems can not hold the password RRS feed

  • Question

  • Hi there:

       I've created a connection manager which is connected to an oracle server. It's our first step to get a fresh copy from oracle to sql server table. Somehow, the connection managers can not store the password for the oracle server. Everytime when I put it in manually, it looks fine. But after I close the pkg and reopen it, it keeps giving me the error invalid username/password. The detailed error msg is something like below:

    "The AcquireConnecction method call to the connection manager "PONLNSRV" failed with error code 0xC0202009...."

    Any idea why the connection manager can not hold the password?




    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --
    Tuesday, April 20, 2010 8:10 PM

All replies

  • Hi,

    I couldn't figure out yet why connection manager can not hold the password. But there is a work around.

    - create SSIS package configuration for the package. chose SQL server configuration type.

    -  select 'ConnnectionString' of the Oracle server connection you already created as property to include in SSIS configuration table.

    - Deploy the package on the server.

    - In the ConfiguredValue field against this property in SSIS configuration table, append the existing connection string by password for the particular connection as password=XXXXX

    so the configuredvalue should look like

    Data Source=[server namne];User ID=[user name];Provider=MSDAORA.1;Persist Security Info=True;password=[password]

    Now this may not work for someone who dont want to display password in SQL table even to people who have access to that database. But it could solve your problem.



    Tuesday, April 20, 2010 11:58 PM
  • What is the package protection level?
    SSIS Tasks Components Scripts Services |
    Wednesday, April 21, 2010 11:05 PM
  • it's EncryptSensitveWithPassword

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --
    Wednesday, April 21, 2010 11:39 PM