ERRORCODE=-4214, SQLSTATE=28000 from DB2 on Ubuntu

Updated 28th October, 2010
Newer versions of DB2 address this problem. Read about it here.

Two posts in one day, wow. It’s all part of our special series: how to install and configure WebSphere Portal 6.1 on Ubuntu. This isn’t a Portal only issue, rather it’s a DB2+Ubuntu issue.

After getting Portal installed on this Ubuntu machine, you’re probably going to want to transfer the default Derby database to something more robust like DB2. So you edit wkplc_comp.properties and wkplc_dbtype.properties, and start to run:

./ConfigEngine.sh create-database

And you get this in the ConfigTrace.log

  [sqlproc] action: execute-sql-scripts
  [sqlproc] _________________________________________________________
  [sqlproc] Database autocommit parameter true
  [sqlproc] No delimiter has been specified, using [;] to separate the SQL statements.
  [sqlproc] Reading file /opt/WebSphere/wp_profile/ConfigEngine/config/database/work/db2/createBufferpools.run
  [sqlproc] Could not connect to database
  [sqlproc] com.ibm.db2.jcc.b.ao: [jcc][t4][2010][11246][3.53.70] Connection authorization failure occurred.  Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000
BUILD FAILED

Hmm, ok, I thought db2 was working. A good habit when debugging these things is to take the piece that ConfigEngine is trying run and run it independently. So right now I want ConfigEngine to create an empty db2 database that I can run database-transfer against. Try this:

su - db2inst1
db2 create db WP610 using codeset UTF8 territory au pagesize 8192

And that comes back successfully. However, that command sequence is not an accurate representation of what ConfigEngine is actually doing. We’re running ConfigEngine as root. But the ConfigEngine script is using the “user db2inst1 using ” modifiers on the end of the database create command. So how about this?

db2 create db WP610 using codeset UTF8 territory au pagesize 8192 user db2inst1 using password
SQL30082N  Security processing failed with reason "15" ("PROCESSING FAILURE").
SQLSTATE=08001

Ah ha, a failure. In the first example, DB2 already trusts the user that we’re logged is as (db2inst1), so it doesn’t need to go back to the operating system and authenticate it. In the second example, we are logged in as root, so db2 needs to go to the operating system and authenticate the user. Ubuntu uses the tried and true passwd + shadow file combo to store usernames and their associated passwords. The trouble is since Ubuntu 8.10, it uses the newer and more secure SHA512 hashing function to store the passwords, and DB2 doesn’t understand SHA512. So the workaround is to change the hashing function in use on the machine, reset the password and then we should be able to use the “user db2inst1 using ” type commands again.

Open /etc/pam.d/common-password in a text editor and change this line:

password        [success=1 default=ignore]      pam_unix.so obscure<strong> sha512</strong>

to

password        [success=1 default=ignore]      pam_unix.so obscure <strong>md5</strong>

Then run passwd db2inst1 and put the same or a new password. If you look at the shadow file , the hash will change from something like this:
SHA512

db2inst1:$6$IKe6x6Zq$bSajPzHNIy7jQrPXbI8CrPRlpDYUVm8.A2BhNCxes5cY6LWoh7hQr14XW4agBWbW1ywKkSSDSLFV.NXCr2/1z0:14368:0:99999:7:::

MD5

db2inst1:$1$FF0YDtZn$gemqCKt4Ml375mhiBXk2U/:14368:0:99999:7:::

(The unencrypted password here is ‘password’ – don’t get too excited!) .

Now try running ConfigEngine.sh create-database again. It should work. Make sure you change the system /etc/pam.d/common-password back to sha512, as you want the rest of your users to use this hashing function as it is more secure than md5sum . Hopefully DB2 should address this in a fixpack.

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

8 Responses to ERRORCODE=-4214, SQLSTATE=28000 from DB2 on Ubuntu

  1. graham says:

    I’ve also recently seen this problem on Fedora 10 as well. The only change to note is that the file to edit is /etc/pam.d/system-auth , not /etc/pam.d/common-password .

  2. cem says:

    Well that saved. Thx!

  3. Graham says:

    Cheers Dave, thanks for the link!

  4. fondfire says:

    I found using the

    chpasswd

    command on CentOS would set an MD5 password hash without requiring you to change any system files. I think that’s important on an active system.

  5. Graham says:

    Hi Fondfire – thanks so much for the tip, that’s a much better way to do it.

  6. Akio Hamasaki says:

    I would also check the case of the user id being used to connect to the DB. Using the proper case (for eg. all lower case or upper case) worked for me for this issue.

  7. Nick says:

    Thanks! Worked like a charm.

Leave a Reply

Your email address will not be published. Required fields are marked *