Expired Passwords and the Oracle Provider

I ran into a problem a while back that caused me a lot of grief so I thought I’d post it here. The package in question was used to traverse hundreds of Oracle databases and run the same query on all of them. This data was then loaded into a data mart. The databases all had an Oracle user on them with a predetermined username and password. It was also in an environment where Oracle Internet Directory (OID) was in use. So it was fairly straightforward to use an SSIS Expression to build the connection string dynamically as the only thing that had to change was the Oracle SID. The connection string looked like this:

    “Provider=OraOLEDB.Oracle;User ID=DBUser;Password=DBPassword;Data Source=OracleSID;”

The package worked flawlessly in development. But when it was deployed to production, it would hang on connections to specific servers. After beating my head against the wall for a while, I discovered that on every server it would fail on, the password was expired for the account. It turns out that by default, the Oracle provider will pop a dialog box for an expired password, prompting a password change. In something batch based like SSIS, this results in the process hanging. Adding a switch to the connection string to suppress the dialog box did the trick.

    “Provider=OraOLEDB.Oracle;User ID=DBUser;Password=DBPassword;Data Source=OracleSID;PwdChgDlg=0;”

This strikes me as something that should default to 0 but in any case it’s one of those little things that is not a big deal until it happens to you and can be tough to track down.

Please share this

This Post Has 3 Comments

  1. Jeffrey Sun

    Hi, Randy
    I run into the same problem and your post save me a lot of time. Many thanks!

  2. Rio

    How this switch will help you when password expired and not opening password change dialog? You will still get Password expired exception right ?

    1. Randy Knight

      It has been a long time since I wrote this, but as I recall, the issue was that it would throw the dialog and just hang vs. throwing an exception that you could catch and deal with. You are correct that it’s still not going to work if the password is expired but we want to be able to catch that in code and deal with it.

Leave a Reply

Related Articles

A toy machine and a microphone on a table Description automatically generated

The Right Tool for the Right Job

I’m looking forward to 2024. From many perspectives, I think we are going to see great advancements, including in technology, that on one hand will make our lives easier, but also make our decisions a bit harder.

Read More »