Saturday, April 11, 2009

DB2 Authentication & Password Encryption Method

Few days ago, I received an e-mail from my colleague with following error message

I verified the password of db2inst1 on OS level and the password “db2test” was correct. I verified the authentication related configuration parameters and everything looked okay. The authentication type was set to SERVER. The connection was working fine if we only run “db2 connect to test” and do not specify user and using keywords. The version of DB2 was 8.1 and the OS was SUSE Linux Enterprise Server 10.

You might be thinking how come I am still dealing with DB2 version 8.1. Well, as I have mentioned in my earlier posts, the company I work for (Stratavia Corporation) has software product for data center automation called
Data Palette. Data Palette still supports DB2 V8.1 and hence we have to test it on DB2 V8.1 also. Going back to the original problem, I approached our system administrator to figure out what is so unique about db2inst1 password that is causing the above authentication error. We found that the password encryption method used for db2inst1 was different than the other V8.1 instances where we don’t have the above mentioned authentication problem. On this problematic system, the password encryption method was “blowfish”. We changed it to “md5” as shown in the portion of “/etc/default/passwd” below. On SUSE Linux, “/etc/default/passwd” has the configuration parameter to control the password encryption method.

After changing the password encryption method, we set the password of db2inst1 and it was encrypted using md5 this time. On Linux systems, you can check the encrypted password in /etc/shadow file (you have to be root). The length of blowfish encrypted password is almost double than md5 encrypted password. Also, I think md5 encrypted password starts with $1 and blowfish encrypted password starts with $2.

After the above change, the authentication problem was gone. So it appears DB2 V8.1 (Fix pack 0) does not support authentication with password that is encrypted using blowfish encryption. Out of curiosity, I upgraded the same instance to V8.2 to test if blowfish password encryption will work or not and with V8.2 blowfish encryption worked fine. Hence, we should keep in mind that DB2 authentication (while using OS authentication) has dependency on password encryption method used by OS. The user password may work fine on the OS level, but DB2 authentication may still fail if DB2 does not support the password encryption method used by OS.