OpenAM database connectivity with MySql

This post comes after a long time. I had been really stuck with my project in creating a Single Sign On implementation. I was working with an amazing piece of software, OpenAM, formerly OpenSSO, currently maintained by the Forgerock community. 

My setup: I used Tomcat with Mysql and OpenAM 11.0.0 running on Centos

So first things first, install mysql-connector-java for your operating system and you should get a jar file. Here is what I got on my box:

# rpm -ql mysql-connector-java | grep jar
/usr/share/java/mysql-connector-java-5.1.17.jar
/usr/share/java/mysql-connector-java.jar

Now copy this to your tomcat installation directory. At "$CATALINA_HOME/lib" and restart tomcat.


If you skip the above step, you will run into an error that looks like this:

java.lang.InstantiationException: JdbcSimpleUserDao.initialize: failed to load driver class jdbcDriver=com.mysql.jdbc.Driver exception=com.mysql.jdbc.Driver
    at com.sun.identity.idm.plugins.database.JdbcSimpleUserDao.initialize(JdbcSimpleUserDao.java:274)
    at com.sun.identity.idm.plugins.database.DatabaseRepo.initialize(DatabaseRepo.java:429)
    at com.sun.identity.idm.server.IdRepoPluginsCache.constructIdRepoPlugin(IdRepoPluginsCache.java:475)
    at com.sun.identity.idm.server.IdRepoPluginsCache.addIdRepo(IdRepoPluginsCache.java:353)
    at com.sun.identity.idm.server.IdRepoPluginsCache.removeIdRepo(IdRepoPluginsCache.java:251)
    at com.sun.identity.idm.server.IdRepoPluginsCache.organizationConfigChanged(IdRepoPluginsCache.java:646)
    at com.sun.identity.sm.ServiceConfigManagerImpl.notifyOrgConfigChange(ServiceConfigManagerImpl.java:493)
    at com.sun.identity.sm.ServiceConfigManagerImpl.objectChanged(ServiceConfigManagerImpl.java:453)
    at com.sun.identity.sm.SMSNotificationManager.sendNotifications(SMSNotificationManager.java:289)
    at com.sun.identity.sm.SMSNotificationManager$LocalChangeNotifcationTask.run(SMSNotificationManager.java:365)
    at com.iplanet.am.util.ThreadPool$WorkerThread.run(ThreadPool.java:306)

Next up, connect to your mysql server and navigate to this page in OpenAM (Access Control -> Realm (of your choice) -> Datastores -> New):

Now click on next. We just need to change the following fields:

Enter the password and username of your mysql database user. Also change the IP address, port and database name of your mysql database installation to refer to a table you have specifically reserved for OpenAM to use. OpenAM will be using 2 tables in this database, the names of which you need to specify  here:

and here:

So for the user table, you need to create the columns as VARCHAR, somehow integer did not work for me. The column names which you need to have in your table can be found in this table here:

I removed all the iplanet_* attributes and created an user table in MySql Database. Then used the following sql script to create the database entries for default configuration:

create database test;
use test;
create table opensso_users (uid varchar(50), userpassword varchar(50), inetuserstatus integer, cn varchar(50),mail varchar(50),manager varchar(50), preferredlocale varchar(50), givenname varchar(50), telephonenumber varchar(50), telephonenumber varchar(50), telephonenumber varchar(50), sn varchar(50)  );
create table groups (uid varchar(50), group_name varchar(50), cn varchar(50));

Now add your users to this table and go, go, go!

NB: I could not get groups working with this configuration, if you have any know-how, please let me know

Read also:

[1] https://wikis.forgerock.org/confluence/display/openidm/JDBC+Repository
[2] Mailing list entry on database configuration