Integrating SQL Database with Virtual Member Manager (i.e. Federated Repository) in WebSphere Application Server (WAS)

While working with one of my clients, I came across a situation where we needed to configure users and groups in an external SQL database, as well as configure user and group management in their Lightweight Directory Access Protocol (LDAP).

The requirement was to have the SQL database shared among multiple WebSphere instances running on different servers. To fulfil this requirement, we used the Virtual Member Manager (VMM)—also known as a federated repository—a feature available in WebSphere Application Server.

VMM provides user and group management and provides the ability for users to achieve a single view of their own multiple repositories in a federated model.

These repositories can be file-based (the default with WAS), LDAP-based, or based on an external database.

The client had internal users and groups that were maintained by the internal LDAP, but they also needed external users and groups that were not part of the LDAP to be maintained in the SQL database and given access to the system.

So we needed to configure the SQL database to act as VMM database and maintain users and groups for authentication.

The picture below depicts a simplistic view of the VMM architecture.

 

Here are the steps needed to Integrate VMM SQL database with WebSphere Application Server.

Go to the Command Prompt and then to <WAS Root>\bin. Type the wsadmin command. Then run this command:

1. $AdminTask setupIdMgrDBTables {-schemaLocation <WAS Root>\etc\wim\setup -dbPropXML <WAS Root>\etc\wim\setup\wimdbproperties.xml -databaseType sqlserver -dbURL jdbc:sqlserver://<DB Host>:<DB Port>;databaseName=<DB Name>; -dbAdminId <DB Admin User>-dbDriver com.microsoft.sqlserver.jdbc.SQLServerDriver -dbAdminPassword <DB Admin Password> -dn o=<your entity> -reportSqlError true}

Note: Your entity can be any name—for example, abc.xyz.com—but it must match the later commands below.

2. $AdminTask createIdMgrDBRepository {-id <Repository Name> -dataSourceName <Your Data Source Name> -databaseType sqlserver -dbURL jdbc:sqlserver://<DB Host>:<DB Port>;databaseName=<DB Name>; -JDBCDriverClass com.microsoft.sqlserver.jdbc.SQLServerDriver -dbAdminId <DB Admin User> -dbAdminPassword <DB Admin Password> }

Note: Define your repository name. This is the name that is visible in the admin console.

3. $AdminTask addIdMgrRepositoryBaseEntry {-id <Repository Name> -name "o=<your entity>" -nameInRepository "o=<your entity>"}

Note: Your entity name must match the value that was defined when running the first command. The nameInRepository value can be same or different. This value is visible in the admin console.

4. $AdminTask addIdMgrRealmBaseEntry {-name "defaultWIMFileBasedRealm" -baseEntry "o==<your entity>"}

5. $AdminConfig save

After running these commands and restarting the application server, the SQL database repository will be available in the admin console.

Note: At present, you cannot add the VMM database through the admin console, though you can add LDAP and a file-based repository.

If you are sharing your VMM database with multiple instances of WAS, you don’t need to run the first command, as the tables in the database have already been created. If you try to run it, it will throw an error complaining that the database tables have already been created. Skip the first command, but run the others.

There is currently a bug in the WAS admin console which does not allow for the creation of users from other instances. Users can be created from the first instance. PMR 47524,L6Q,000 has been opened with IBM to tackle this issue.

 

Alok Keshri

Alok Keshri
Advisor/Principal Architect

Alok Keshri is an advisor and Principal Architect for Prolifics, focusing mainly on IBM WebSphere technologies, Smarter Process, and Cloud-based solutions. He has over 16 years of experience in IT, spanning various industries. He is an experienced and resourceful enterprise software architect and has worked with J2EE, SOA, BRMS, BPM, IIB, and Cloud technology. He specializes in object-oriented analysis, design, development, and project estimation with heterogeneous Web technologies in complex business systems based on variety of platforms. He has extensive experience in IBM middleware products such as WebSphere Application Server, WebSphere ILog-JRules, Operation Decision Manager (ODM), WebSphere Message Broker, IBM Integration Bus, IBM Bluemix, Cloud-based solutions, and other products in the IBM BPM stack. He is certified in WebSphere ILog-JRules, ODM, and Filenet. Alok received his BE in Electrical Engineering in 2000.