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
Tags
#microsftcertifedmaster #microsftpartner #parallelism #azure #SQLAB #SQlatino #SQlatinoamerica #sqldatabase #sqldeveloper #SQLgroupie #sqlimer #sqlimerbymay #sqlinjection #sqlinternals #sqlite #sqlite3 #SQLLearning #SQLMagazine #sqlmanagementstudio #sqlmanager #Sqlmap #sqlrun #sqlsaturday2017 #sqlsatvienna #sqlserver #SQLserver2012 #sqlserver2014 #sqlserver2017 #sqlserver2022 #SQLServeronLinux #SQLsolutionsgroup #SQLTraining #SQLYog Events Extended Events learning SQL SQL Administration SQL Internals SQLPASS SQLSaturday SQL Saturday SQL Server SSG SSIS T-SQL
Picture of Randy Knight

Randy Knight

Randy Knight, a Microsoft Certified Master, founded SQL Solutions Group in October 2010, after more than 20 years in the technology industry. Randy has a passion for solving what can seem to others to be insurmountable problems. His prowess with SQL Server earned him the label “database whisperer” from one satisfied customer. Randy is a Microsoft Certified Solutions Master in SQL Server 2008. In 2020 he completed certification for Azure Fundamentals. In addition, he has numerous Novell, Microsoft, and Cisco legacy certifications. Prior to founding SSG, he spent many years as a Database Administrator in a variety of industries, including financial services, telecommunications, and with well-known dot com properties. As a data professional, Randy says he has done lots of fun stuff, encountering the most interesting data during his time as database architect for match.com (and no, he didn’t meet his wife while working there). In addition to getting elbow-deep in data, Randy enjoys taking part in and presenting at SQL Server events nationwide, including SQLSaturday, User Groups, SQL PASS Rally and SQL PASS Summit. In Randy’s words, “We've assembled a great team of SQL Server Consultants at SQL Solutions Group, and we have the talent to tackle anything."

This Post Has 3 Comments

  1. Jeffrey Sun 2 Feb 2012 Reply

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

  2. Rio 20 Nov 2023 Reply

    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 1 Dec 2023 Reply

      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