Microsoft Ole DB Provider for DB2: The Evil Bang!

Just a quick note from earlier in the week.  After successfully using the Microsoft OLE DB Provider for DB2 for many many MANY packages in the past to communicate with our DB2 servers on various platforms we had a problem earlier in the week.  Just as a bit of background information, we had finished moving a pass through service server which federated one of our Mainframe DB2 servers from a physical box to a virtual box.  It was some work, but afterwards everything appeared to be going good.  We had run with the new packages for a few weeks with no problems…. Then, along comes a requirement to test out the Disaster Recovery (DR) initiative which uses the same technology but will point to a new server down south.  The package, with our current framework, should just be able to accept a new connection string and roll with no problems right? Wrong!

Well, they tried this out on one of our test servers and it crashed.  We were getting authentication errors coming from the provider.  I was not part of the original troubleshooting process, but after a small amount of time, the group that was responsible had changed the production user name and password pair to match the new DR user name and password so that they could simply change the server name… makes sense right?  Well, now, neither the connection to the production server or the DR server was working, and it was getting to be late in the day.  Here, I come in and am told by one of the DBAs that this won’t work with the Microsoft provider and that we should switch to the IBM provider and that he had never used the Microsoft one and didn’t trust it.  He pointed to the fact that he could pull up the configuration assistant and demonstrate that the current user name and password worked on both environments.  Lucky me.

The bottom line was, now it wasn’t working on either environment for my team.  However, it WAS working at the beginning of the day…  I knew that something small had to be wrong.  And that is exactly it, it WAS something small.  But nothing that we did made it apparent or showed us what was happening.  After banging my head against the wall for several hours I threw a shot out in the dark… Our password had an exclamation point as a special character.  Could that possibly be the culprit?  No, it couldn’t be!  But, running out of time and becoming very grumpy and frustrated I asked for the DBA to switch it and test it out.

I’m not exactly sure why the exclamation point caused this issue, I just know it’s true.  When we changed the password back to the original, everything started running smooth again.  After looking online for a bit, I saw a few articles talking about how the bang is the default execute mark for db2.  Perhaps it was that, or perhaps it is just something with the provider.  Either way, if you are having problems with the Microsoft Ole DB2 provider, and your password has a bang! Try testing this out…

Advertisements
This entry was posted in SSIS and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s