Why Did Passphrase Activation Take Out My ODBC Connection?

November 17, 2010 Hey, Joe

We just activated passphrase support on our i5/OS V5R4M5 system. After activation, one of my Web server ODBC connections stopped working and I have no clue how to reset it. How can I get it working again?

–Keith

After getting this email, I contacted Keith and ran through a few scenarios. Here’s how we solved this issue as well as another issue that was hiding right behind it.

First, we found that Keith was using an embedded password for his ODBC connection, which resided on a Web server that was automatically updating i5/OS data. The password was set to “LETMEIN” (all capitals). I tried signing on with that password and the system wouldn’t let me on. On a hunch, I then tried signing on to the system as the same user but this time I used “letmein” as the password (all lowercase letters). That worked and I was able to get on the system.

What I think happened was that when the Password level system value (QPWDLVL) was set to 1, system passwords could only contain a maximum of 10 characters and the system didn’t care about the case-sensitivity of its stored passwords. When Keith’s i5/OS box changed its password level to 2, it activated the following additional operating system support for passwords.

Longer passwords could be entered.
Almost every keyboard character could be used in a password.
System passwords became case-sensitive.

My theory is that the new case-sensitivity requirement bit Keith in this case. The original password “LETMEIN” may actually have been entered and stored in i5/OS as all lowercase letters. This wasn’t a problem with password level 1 when i5/OS was just trying to match the incoming password, regardless of case. But when password level 2 was activated, suddenly Keith’s ODBC connection was sending over an uppercase password of “LETMEIN” when the i5/OS system wanted a lowercase “letmein” password, and the system rejected the login.

The Plot Thickens

So we changed the ODBC i5/OS user profile passphrase to “LETMEIN” so that it would match the embedded uppercase password in his Web site ODBC connection. Then Keith tried to make his automatic connection and to our surprise, the Web site connected but the application wouldn’t work. This time, we got the following SQL error.

SQL0901 - SQL System Error

Fortunately, I had encountered this problem before. The SQL0901 error is most commonly caused by SQL dynamic package support issues. Package support tells i5/OS to save the data access plans that the query optimizer creates for your prepared SQL statements. Access plans are saved in an i5/OS object called an SQL package (object type *SQLPKG), and they are saved whenever a client submits any new SQL statement to your i5/OS box through an ODBC, JDBC, or OLE DB driver. The name of the default Database Host Servers SQL package is QZDAPKG and it resides in the QGPL library.

What occasionally happens is that QZDAPKG can become unusable because of the following issues:

A system upgrade has occurred and the old package is incompatible with the new operating system
Object corruption
It hits a size limitation

I had written about this issue a few years ago in an article called One Common Cure for SQL0901 Package Errors. To fix the problem, you need to delete the partition’s database host server package (QZDAPKG) so that the system will recreate a new package that will work again.

Deleting the old package is quick and easy, but there’s one catch.

QZDAPKG is usually locked by the system’s database server prestart jobs so you have to unlock the object to delete it. You can unlock QZDAPKG by ending the Database Host Server jobs as well as the three database server prestart jobs that access QZDAPKG. But beware. Taking down these servers during the daytime will also disable any remote jobs that are trying to connect to your partition through ODBC, OLE DB, JDBC, and possibly Client Access Data Transfers. So we had to find a time when we could take down these jobs without disturbing Web site processing. We found an open maintenance window time at 4 a.m. the next morning.

IBM provides procedures for recreating the QZDAPKG package in support document 86256FF500732959, Deleting the Database Host Server’s SQL Package. We retrieved and ran the procedures during the maintenance window. This process recreated QZDAPKG and fixed the problem.

HTH

–Joe

One Common Cure for SQL0901 Package Errors

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Tags:

CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

“Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”¹

Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS. Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

ucgtechnologies.com/triple-play

800.211.8798 | info@ucgtechnologies.com

iTech to Resell CODA Financials for IBM i Whaaa? IBM Gets Stingier with Power Systems Deals

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search