LDAP Directory & SQL Database - Syncing the Unsyncable?

While I would like to start this article by greeting you all and writing all that good stuff people put at the top but, let's just start by noting that we live in a world where nothing is perfect or at least the stuff humans come up with. One thing works great for one specific problem but, not so good for others. Like the IT guys would tell you how Directory Services are so good at managing users, computers, their access and security so, they would always like to keep that thing up and running. But, you, the developer, know how easy it is to query, sort, filter and associate data in Databases. So, how can we have the best of both? Of course, you could make something along the lines of DirectoryBase (something I totally made up just now) and for that I already wish you good luck. Or, we, the humble ones can continue along with syncing common data between the two services i.e. Directory Service and Database Service.

You can think of syncing as keeping up with the changes i.e. when one thing changes, the other must too OR vice versa. Let's just discuss a case where you are developing an application that manages feedback about employees in your company. Your company already has every employee registered in their Directory Service and want to keep feedback items for each of their employees and the employee who gave that feedback. One way to architecture this simple application would be get access to the Directory Service and save feedback items as that employee's attributes but, this would not give you freedom of querying, sorting and filtering like you would have in a database. So, a better alternative would be to somehow bring the basic data about employees in a database table and create another table of feedbacks that keeps the feedback items.

But, we're so humbly saying that we will fetch employee records from Directory Service and put them into our Database. What if some existing employee leaves, a new employee comes in or worst, some employee got his name or email changed? That's where we need to sync data b/w Directory Service and Database. Notice, we're considering Directory as source while Database as our destination. In some other scenario, you might want to have Database as source and Directory as destination. In very extreme cases, both Directory can act as source and destination. But, let's be humble and tackle our employee-feedback application with Directory as source and Database as destination.

Where to start, you ask? I would suggest asking your very best internet friend Google. Just type your problem and let Google bring you solutions from the world of internet which is unless you want to reinvent several broken wheels yourself. However, since, I am writing this, I have already googled "syncing ldap and sql". The most popular result was this open source LDAP Synchronization Connector thingy aka. LSC.

Now, before I go very specific about how LSC can help you avoid reinventing the wheel, I just want to clear a few things. Directory Service is a general concept and there are a lot of implementations of this concept on different platforms. For Windows, we have Active Directory Directory Services (yes, it's that long); for Linux, we have OpenLDAP. But, what unites all these implementations is this Lightweight Directory Access Protocol aka. LDAP. You can grab a LDAP implementation and talk to any platform-specific directory service. Hurray! And, that's what LSC builds upon. It syncs to any directory service using this LDAP protocol we just discussed. Ain't this world a beautiful place all of a sudden? Also, I would like to point out that specific implementations of these Directory Services might already give you tools to sync changes with some other application like Active Directory on Windows does. Active Directory allows you to poll for changes or subscribe to changes, implementation of which must be done by you, the developer. Active Directory recommends polling for changes is insanity: Checking again and again and expecting a lot of shit to change.

LSC boasts itself to have already fixed some very strange bugs you would run into if you decide to implement your own DirectorySync Ninja - Ultimate Version (perhaps, something more catchy whatever). So, that's one reason to stick to LSC and the second reason would be that I have already gotten my hands dirty while fiddling around with it and would totally recommend it (mainly because it gets the job done).

Before we can carry out this exciting experiment, you would need to setup following things:

1. LSC
2. Java
3. Directory Service
4. SQL Database
5. LSC Config

Notes about LSC

To install LSC on Ubuntu or Debian based Linux distro, you can do by following this guide on LSC website. If you're using some other Operating System, you can check out the Download page on LSC website.

Notes about Java - LSC dependency

Since, LSC depends on Java, do setup Java on your machine. LSC documentation wants you to grab Java 6 but, I tried it with Java 8 and everything almost works. Java 9 or above will not probably work as the code is bit old, I believe. It definitely didn't work on Java 11. Continuing your Java setup, make sure JAVA_HOME is in your PATH variable and if it's not, please do add it.

Notes about Directory Service

For directory service, you can use any implementation of it. However, if you don't have something up and running, you can always use this online LDAP server. If you don't have a shiny LDAP client, you can always use the one here.

Notes about Database Service

For database, you can use any SQL implementation. I used MySQL. You can install and set it up honestly using any guide on the internet. If you're reading this, you probably already have it installed. Note: Whatever implementation you choose, you must also install a JDBC Driver. For MySQL, you can find out how to install it here.

LSC Config

Now, you're all done with installing stuff. LSC works by requiring you to edit a config file that is located at /etc/lsc/lsc.xml if you're on Linux. This config file would tell LSC what services to connect to, what services to use as Source and Destination and the rules that may specify how the syncing process is carried out.

Here's a sample config file that will work just fine to start out for our employee feedback application:

<?xml version="1.0" ?>
<lsc xmlns="http://lsc-project.org/XSD/lsc-core-2.1.xsd" revision="1">
  <connections>
    <ldapConnection>
      <name>ldap-src-conn</name>
      <url>ldap://www.zflexldap.com:389/dc=zflexsoftware,dc=com</url>
      <username>cn=ro_admin,ou=sysadmins,dc=zflexsoftware,dc=com</username>
      <password>zflexpass</password>
      <authentication>SIMPLE</authentication>
      <referral>IGNORE</referral>
      <derefAliases>NEVER</derefAliases>
      <version>VERSION_3</version>
      <pageSize>-1</pageSize>
      <factory>com.sun.jndi.ldap.LdapCtxFactory</factory>
      <tlsActivated>false</tlsActivated>
    </ldapConnection>
    <databaseConnection>
      <name>jdbc-dst-conn</name>
      <url>jdbc:mysql://127.0.0.1:3306/lsc</url>
      <username>root</username>
      <password></password>
      <driver>java.sql.DriverManager</driver>
    </databaseConnection>
  </connections>
  <tasks>
    <task>
      <name>Employees</name>
      <bean>org.lsc.beans.SimpleBean</bean>
      <ldapSourceService>
        <name>openldap-source-service-employee</name>
        <connection reference="ldap-src-conn" />
        <baseDn>ou=users,ou=developers,dc=zflexsoftware,dc=com</baseDn>
        <pivotAttributes>
          <string>uid</string>
        </pivotAttributes>
        <fetchedAttributes>
          <string>cn</string>
          <string>uid</string>
        </fetchedAttributes>
        <getAllFilter>(&amp;(objectClass=person)(uid=*))</getAllFilter>
        <getOneFilter>(&amp;(objectClass=person)(uid={uid}))</getOneFilter>
        <cleanFilter>(&amp;(objectClass=person)(uid={uid}))</cleanFilter>
      </ldapSourceService>
      <databaseDestinationService>
        <name>mysql-src-service-client</name>
        <connection reference="jdbc-dst-conn" />
        <requestNameForList>getEmployeeList</requestNameForList>
        <requestNameForObject>getEmployee</requestNameForObject>
        <requestsNameForInsert><string>insertEmployee</string></requestsNameForInsert>
        <requestsNameForUpdate><string>updateEmployee</string></requestsNameForUpdate>
        <requestsNameForDelete><string>deleteEmployee</string></requestsNameForDelete>
      </databaseDestinationService>
      <propertiesBasedSyncOptions>
        <mainIdentifier>srcBean.getMainIdentifier()</mainIdentifier>
        <defaultDelimiter>;</defaultDelimiter>
        <defaultPolicy>FORCE</defaultPolicy>
        <conditions>
            <create>true</create>
            <update>true</update>
            <delete>true</delete>
            <changeId>false</changeId>
        </conditions>
      </propertiesBasedSyncOptions>
    </task>
  </tasks>
</lsc>

If you've reached this far and are clueless, don't be. I will soon be updating this article. Just that my brain has now just jammed for the day plus, I just want to see whether people are actually interested in more of this.

Now, in the config file, you will see the following lines:

    <ldapConnection>
      <name>ldap-src-conn</name>
      <url>ldap://www.zflexldap.com:389/dc=zflexsoftware,dc=com</url>
      <username>cn=ro_admin,ou=sysadmins,dc=zflexsoftware,dc=com</username>
      <password>zflexpass</password>
      <authentication>SIMPLE</authentication>
      <referral>IGNORE</referral>
      <derefAliases>NEVER</derefAliases>
      <version>VERSION_3</version>
      <pageSize>-1</pageSize>
      <factory>com.sun.jndi.ldap.LdapCtxFactory</factory>
      <tlsActivated>false</tlsActivated>
    </ldapConnection>

With that piece of code, we're telling LSC how to connect to our LDAP server. For now, I've put in credentials of an online LDAP server which you can use, too. But, you can always put in your own LDAP server's credentials.

Similarly, we define another connection but, this time, it's a database connection using the following lines:

    <databaseConnection>
      <name>jdbc-dst-conn</name>
      <url>jdbc:mysql://127.0.0.1:3306/lsc</url>
      <username>root</username>
      <password></password>
      <driver>java.sql.DriverManager</driver>
    </databaseConnection>

I've put in default MySQL configuration. If you know your credentials specifically, do edit as you please.

Now, that we have let LSC know how to connect to our services, it's time to define what data should LSC take from LDAP and make it available to be put into the Database, we do so by defining a task of syncing employees from the LDAP server to our Database as follows:

   <task>
      <name>Employees</name>
      <bean>org.lsc.beans.SimpleBean</bean>
      <ldapSourceService>
        <name>openldap-source-service-employee</name>
        <connection reference="ldap-src-conn" />
        <baseDn>ou=users,ou=developers,dc=zflexsoftware,dc=com</baseDn>
        <pivotAttributes>
          <string>uid</string>
        </pivotAttributes>
        <fetchedAttributes>
          <string>cn</string>
          <string>uid</string>
        </fetchedAttributes>
        <getAllFilter>(&amp;(objectClass=person)(uid=*))</getAllFilter>
        <getOneFilter>(&amp;(objectClass=person)(uid={uid}))</getOneFilter>
        <cleanFilter>(&amp;(objectClass=person)(uid={uid}))</cleanFilter>
      </ldapSourceService>
      <databaseDestinationService>
        <name>mysql-src-service-client</name>
        <connection reference="jdbc-dst-conn" />
        <requestNameForList>getEmployeeList</requestNameForList>
        <requestNameForObject>getEmployee</requestNameForObject>
        <requestsNameForInsert><string>insertEmployee</string></requestsNameForInsert>
        <requestsNameForUpdate><string>updateEmployee</string></requestsNameForUpdate>
        <requestsNameForDelete><string>deleteEmployee</string></requestsNameForDelete>
      </databaseDestinationService>
      <propertiesBasedSyncOptions>
        <mainIdentifier>srcBean.getMainIdentifier()</mainIdentifier>
        <defaultDelimiter>;</defaultDelimiter>
        <defaultPolicy>FORCE</defaultPolicy>
        <conditions>
            <create>true</create>
            <update>true</update>
            <delete>true</delete>
            <changeId>false</changeId>
        </conditions>
      </propertiesBasedSyncOptions>
    </task>

Here are some important parts from our Employees task:

1. baseDN
2. pivotAttributes
3. fetchedAttributes
4. getAllFilter
5. getOneFilter
6. cleanFilter
7. requestNameFor*

baseDN

baseDN defines from where should LSC start looking for LDAP objects. Since, I've used the online zflex LDAP server, I found the users OU in developers OU perfect for our use case. However, you can bind to any OU or DN of your choice. When we will specify our filters, LSC will search objects right under baseDN value. If we were strictly following our employee feedback example, we would have employees OU somewhere in our LDAP which we could bind to.

pivotAttributes

pivotAttributes defines the unique attribute(s) that will identify objects from LDAP. If LDAP has some object with these attributes present but, it's not in our database, that object will be enqueued for insertion. However, If LDAP does not have any object with these attributes but, such object is present in your database, it will be enqueued for deletion.

fetchedAttributes

fetchedAttributes includes pivotAttributes and defines the attribute(s) which will be considered for syncing b/w your LDAP server and database. If any of these attributes get changed in any LDAP object, that object will be enqueued for updation.

getAllFilter

getAllFilter defines how to search for all candidate objects in LDAP under your baseDN. Any object in LDAP that matches our getAllFilter will be available for us to save it in our database.

getOneFilter

getOneFilter defines how to search for a specific candidate object in LDAP under your baseDN. This option is just there to give you flexibility in filtering for a specific object.

cleanFilter

cleanFilter defines how to search for a specific in LDAP under your baseDN which is a candidate for DELETION. Again, its value can just be your getOneFilter but, is there for flexibility.

requestsNameFor*

All requestsNameFor* tags refer to our queries that we will define and look at in a moment. LSC itself does not do the magic of syncing from LDAP to your database but, it only provides mechanism to do it. Whatever it finds in LDAP, either some object yet to inserted, deleted or updated in Database is forwarded to these requests as arguments and then these requests actually specify SQL queries that are ran to sync the LDAP objects to the database. But, I never mentioned how and where these requests should be placed.

Defining Actual SQL Requests

LSC uses iBatis ORM to make everything as easy possible for you. To configure iBatis LSC wants to write another file at /etc/lsc/sql-map-config.xml which LSC will configure iBatis for basic connection details and reference to separate request definition files. Let's take a look at a sample sql-map-config.xml below:

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<!--
    LDAP Synchronization Connector (LSC) - http://lsc-project.org

    This file configure iBatis to read sqlMap files.
    Settings should be generated here automatically.
    The only section you need to check is the sqlMap entries at the bottom.
-->

<sqlMapConfig>

  <!-- The settings element has a number of configuration switches and properties
       that configure the SQL Maps engine. Most are performance tweaks or resource
       management.  They are pretty self explanatory. The defaults are shown here.
       There can be only one settins element.  -->

  <settings
    lazyLoadingEnabled="true"
    cacheModelsEnabled="true"
    enhancementEnabled="true"
    maxRequests="512"
    maxSessions="128"
    maxTransactions="32" 
    useStatementNamespaces="false"
    />

  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property value="${driver}" name="JDBC.Driver" />
      <property value="${url}" name="JDBC.ConnectionURL" />
      <property value="${username}" name="JDBC.Username"/>
      <property value="${password}" name="JDBC.Password"/>
      <property value="15" name="Pool.MaximumActiveConnections"/>
      <property value="15" name="Pool.MaximumIdleConnections"/>
      <property value="1000" name="Pool.MaximumWait"/>
    </dataSource>
  </transactionManager>

  <!-- Finally, the SQL Maps themselves must be registred with the configuration file.
       Below are some examples of some SQL Map reference elements.  Notice that an
       SQL Map can  be located in the classpath (resource) or at a URL (e.g. file://
       or http://).  There can be any number of sqlMap elements. -->

  <sqlMap url="file://${lsc.config}/sql-map-config.d/Employees.xml"/>

</sqlMapConfig>

Notice the <sqlMap url="file://${lsc.config}/sql-map-config.d/Employees.xml"/> line in the file. We're referring to the file Employees.xml in sql-map-config.d directory. You can go ahead and create sql-map-config.d directory and Employees.xml with the following contents in it:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Employees">

    <select id="getEmployee" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        SELECT
            uid,
            name AS cn,
            email AS mail
        FROM Employee
        WHERE uid = #uid#
    </select>

    <insert id="insertEmployee" parameterClass="java.util.Map">
        INSERT INTO Employee
            (uid, name, email)
        VALUES
            (#uid#, IFNULL(#cn#, ''), IFNULL(#mail#, ''))
    </insert>

    <update id="updateEmployee" parameterClass="java.util.Map">
        UPDATE Employee
        SET name = #cn#
        WHERE uid = #uid#
    </update>

    <delete id="deleteEmployee" parameterClass="java.util.Map">
        DELETE FROM Employee
        WHERE uid = #uid#
    </delete>

    <select id="getEmployeeList" resultClass="java.util.HashMap">
        SELECT uid
        FROM Employee
    </select>

</sqlMap>

Once you've done that and modified Employees.xml accordingly. You will be all setup to execute LSC with the task of syncing your company's employees. Just run in the command prompt:

sudo lsc -s Employees

The -s flag tells LSC to run in Synchronization mode and run Employees task. If you've done everything correctly, you will start seeing logs from LSC what objects it found and what it is doing with them. By the end of it all, you'll have all your company Employees from LDAP synced to Database.

BUT ALL IS NOT OVER YET.

You need to register LSC as a CRON job to keep syncing Employees from LDAP to your Database. You can set appropriate time according to the requirements of the application. 5 minutes would do just fine for employee feedback application.

In the next part, I would explain in more detail and would present a more difficult task of syncing groups of users. Stay tuned for that! Don't hesitate to put your thoughts in the comments. Did this article help you in any way? Or do you think it served no purpose? Perhaps, you have a better solution. I'm all ears.

P.S. I did call LDAP-SQL duo unsyncable. They're definitely syncable. But, you would come across a very specific scenario of syncing groups of users which seemingly is unsyncable. Nevertheless, it was nice to have your attention with such title.