Using OpenLDAP with Microsoft SQL Server and Oracle Backend Databases

Contents

Introduction

OpenLDAP is an open source implementation of the Lightweight Directory Access Protocol (LDAP). LDAP is an open-standard protocol for accessing data stored in an information directory. It lets LDAP-aware client applications search for, add, modify and delete directory entries. For example, a user could use an LDAP client to query a directory server for information about specific users, computers, departments or any other information stored in the directory.

By using the Easysoft ODBC-SQL Server Driver with OpenLDAP and the SQL backend, back-sql, you can expose data in Microsoft SQL Server databases to LDAP client applications. By using the Easysoft ODBC-Oracle Driver, you can expose data in Oracle databases to LDAP client applications.

The SQL Backend

A backend defines the type of database OpenLDAP uses to store or generate data. The standard backend, back-bdb, uses Oracle Berkeley DB to store data. The SQL backend, back-sql, lets you publish data stored in relational databases in LDAP form.

back-sql uses metadata to translate LDAP requests to SQL queries. The existing relational database schema remains unchanged. SQL applications can continue to use the database without modification. The same data is available to both SQL applications and LDAP applications, which can interoperate without replication, and exchange data as needed.

back-sql uses ODBC to connect to RDBMSs. back-sql is compatible with the unixODBC Driver Manager. Both the Easysoft ODBC-SQL Server Driver and Easysoft ODBC-Oracle Driver distributions include the unixODBC Driver Manager and we recommend you install that as part of the Easysoft ODBC driver installation.

This tutorial describes how to install and build OpenLDAP with back-sql on Unix and Linux platforms. It shows how to access SQL Server and Oracle databases from OpenLDAP by using back-sql with either the Easysoft ODBC-SQL Server Driver or the Easysoft ODBC-Oracle Driver.

The back-sql distribution includes some example SQL modules for SQL Server and Oracle. Each module contains a sample configuration file that shows how to tune back-sql for the SQL dialect used by the RDBMS. back-sql also provides a set of SQL scripts that you can use to populate your SQL Server or Oracle database with test metadata and sample records. This tutorial shows you how to edit the configuration file to access your database, import the sample SQL and retrieve the test data from an LDAP client application.

For more information about the metadata that you need to create to define mappings between entries in your LDAP tree and the corresponding data in your database, see the slapd-sql(5) man page and the OpenLDAP FAQ The SQL backend (How do I setup/configure back-sql?). For sample metadata, examine the file testdb_metadata.sql, one of the example scripts included with back-sql.

Installing the Easysoft ODBC Driver

If you have not already done so, please register with us to download a fully functional free trial version of the Easysoft ODBC driver for the database you want to access from OpenLDAP.

Installing the Easysoft ODBC-SQL Server Driver

If you want to access SQL Server from OpenLDAP, follow these steps:

  1. Download the Easysoft ODBC-SQL Server Driver for the platform on which you want to install OpenLDAP.
  2. Install the Easysoft ODBC-SQL Server Driver on the machine on which you want to install OpenLDAP.

    For installation instructions, see the Easysoft ODBC-SQL Server Driver documentation.

  3. Refer to the Easysoft ODBC-SQL Server Driver documentation for information about the necessary environment variables you need to set for the Easysoft ODBC-SQL Server Driver (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker). You need to ensure that these variables are in your environment when you:
    • Test the Easysoft ODBC-SQL Server Driver with isql.
    • Make and install the OpenLDAP distribution.
    • Start slapd.
  4. Create an Easysoft ODBC-SQL Server Driver data source in the /etc/odbc.ini file that connects to the SQL Server database you want to access from OpenLDAP. For example:
    [my_sql_server_dsn]
    Driver                  = Easysoft ODBC-SQL Server
    Server                  = my_machine\SQLEXPRESS
    User                    = my_domain\my_user
    Password                = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database                = openldap
    
    

    For more information about Easysoft ODBC-SQL Server Driver data source configuration, see the Easysoft ODBC-SQL Server Driver documentation.

  5. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v my_sql_server_dsn

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

Installing the Easysoft ODBC-ODBC Bridge

The Easysoft ODBC-ODBC Bridge (OOB) provide an alternative way to access SQL Server from OpenLDAP. To install the OOB, follow these steps:

  1. Download the OOB Client for the platform on which you want to install OpenLDAP. Download the Windows OOB Server.
  2. Install the OOB Client on the machine on which you want to install OpenLDAP. Install the OOB Server on the Windows computer where the SQL Server ODBC driver is installed.

    For installation instructions, see the OOB documentation.

  3. Refer to the OOB documentation for information about the necessary environment variables you need to set for the OOB (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker). You need to ensure that the relevant variable is set and exported when you:
    • Test the OOB with isql.
    • Make and install the OpenLDAP distribution.
    • Start slapd.
  4. If you have not already done so, in the Windows ODBC Data Source Administrator, create a SQL Server System data source that connects to the SQL Server database you want to access from OpenLDAP.
  5. Create an OOB data source in the /etc/odbc.ini file that points to this SQL Server System data source.

    For example, this sample data source points to an OOB Server on a host named my_windows_server where there is a SQL Server System data source called sql_server_system_dsn. The LogonUser and LogonAuth values are a valid Windows username and password that can be used to log in to my_windows_server.

    [my_sql_server_dsn]
    Driver = OOB
    ServerPort              = my_windows_server:8888
    LogonUser               = my_windows_username
    LogonAuth               = my_windows_password
    TargetDSN               = sql_server_system_dsn

    For more information about data source configuration, see the OOB documentation.

  6. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v my_sql_server_dsn sql_server_username sql_server_password

    where sql_server_username and sql_server_password are a valid SQL Server database login. At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

Installing the Easysoft ODBC-Oracle Driver

If you want to access Oracle from OpenLDAP, follow these steps:

  1. Download the Easysoft ODBC-Oracle Driver for the platform on which you want to install OpenLDAP.
  2. Install the Easysoft ODBC-Oracle Driver on the machine on which you want to install OpenLDAP.

    For installation instructions, see the Easysoft ODBC-Oracle Driver documentation.

    You also need to install the Oracle client software for your Oracle platform on this machine.

  3. Refer to the Easysoft ODBC-Oracle Driver documentation for information about the necessary environment variables you need to set for the Easysoft ODBC-Oracle Driver (LD_LIBRARY_PATH, ORACLE_HOME, for example). You need to ensure that these variables are in your environment when you:
    • Test the Easysoft ODBC-Oracle Driver with isql.
    • Make and install the OpenLDAP distribution.
    • Start slapd.
  4. Create an Easysoft ODBC-Oracle Driver data source in the /etc/odbc.ini file that connects to the Oracle database you want to access from OpenLDAP.

    For example, this sample data source specifies a local net service name that identifies the target Oracle database.

    [my_oracle_dsn]
    Driver = ORACLE
    Database = my_database # If you are using the Instant Client, use this
                        # format for the Database attribute value:
                        # //my_database_host:1521/my_database_service_name
    

    For more information about Easysoft ODBC-Oracle Driver data source configuration, see the Easysoft ODBC-Oracle Driver documentation.

  5. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v my_oracle_dsn my_oracle_username my_oracle_password

    where my_oracle_username and my_oracle_password are a valid Oracle database user name and password. At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

Configuring and Building OpenLDAP

This section shows you how to build OpenLDAP with back-sql.

The prerequisites for building OpenLDAP depend on the features that you require. Please refer to the OpenLDAP FAQ What is prerequisite for building OpenLDAP software?. Note that you must install the Easysoft ODBC driver before building OpenLDAP with the SQL backend.

When testing OpenLDAP and back-sql with Easysoft ODBC drivers, we used:

  1. Download the OpenLDAP distribution from the OpenLDAP web site, unpack and cd into the directory created by unpacking the distribution file. For example:
    tar xfz openldap-version.tgz
    cd openldap-version

    where version is the version number of the OpenLDAP distribution.

  2. Read the following notes then follow the instructions in the INSTALL file.
    • You need to have set and exported the necessary environment variables for your Easysoft ODBC driver before following the instructions in the INSTALL file.

      The following line shows the command we used when testing the Easysoft ODBC-SQL Server Driver with OpenLDAP 2.4:

      LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/lib: \
        /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/sqlserver/lib: \
        /usr/local/lib:/usr/local/BerkeleyDB.4.4/lib/
      export LD_LIBRARY_PATH

      The following line shows the command we used when testing the OOB with OpenLDAP 2.3:

      LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/lib: \
        /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/oob/client: \
        /usr/local/lib:/usr/local/BerkeleyDB.4.2/lib
      export LD_LIBRARY_PATH
    • To enable the SQL backend, you need to include the --enable-sql option on the configure line. You also need to set the necessary environment variables so that the compiler and linker can locate the unixODBC include files and libraries. The unixODBC include files are located in installation_directory/easysoft/unixODBC/include, where installation_directory is the Easysoft installation directory, by default, /usr/local. The unixODBC libraries are located in installation_directory/easysoft/unixODBC/lib.

      The following line shows the settings that we used when configuring the OpenLDAP build system on Red Hat Linux.

      CPPFLAGS="-I/usr/local/easysoft/unixODBC/include \
        -I/usr/local/include -I/usr/local/BerkeleyDB.4.2/include"
      export CPPFLAGS
      LDFLAGS="-L/usr/local/easysoft/unixODBC/lib \
        -L/usr/local/BerkeleyDB.4.2/lib" 
      export LDFLAGS 
      ./configure --enable-sql

Configuring and Testing the SQL Backend

The back-sql distribution includes some sample SQL scripts that you can use to populate your database with test metadata and data. The distribution also includes sample slapd.conf files that are set up for use with a particular database. The sample slapd.conf contains a section for the SQL backend that specifies the ODBC data source used to access the database and configures the default behaviour of the backend using appropriate defaults for the DBMS. back-sql has a number of configuration options that let you:

These options are described in the slapd-sql(5) man page.

The following steps show you how to configure back-sql to access the Easysoft ODBC data source you created earlier and retrieve some test data.

  1. As root, copy the openldap_source_directory/servers/slapd/back-sql/rdbms_depend/dbms/slapd.conf file to the /usr/local/etc/openldap directory.

    Replace openldap_source_directory with the path to the directory created by unpacking the OpenLDAP distribution file. Replace dbms with either Oracle or mssql.

    The OpenLDAP installation automatically creates a backup copy (/usr/local/etc/openldap/slapd.conf.default) of the default slapd.conf file.

  2. As root, open /usr/local/etc/openldap/slapd.conf in a text editor.
  3. In the SQL database definition section, change the default values of the following settings:
    SettingValue
    dbnameThe name of the Easysoft ODBC driver data source that you configured when you installed the Easysoft ODBC driver.
    dbuserA valid SQL Server or Oracle database user name.
    dbpasswdThe password for the user name you specified with dbuser.
  4. Change the suffix and rootdn values so that they match the distinguished name (DN) specified in the back-sql sample metadata:
    # suffix		"o=sql,c=RU"
    suffix		"dc=example,dc=com"
    # rootdn		"cn=root,o=sql,c=RU"
    rootdn		"cn=root,dc=example,dc=com"
  5. Add this line to the end of the section:
    has_ldapinfo_dn_ru	no

    The has_ldapinfo_dn_ru statement tells the SQL backend whether the dn_ru column is present in the ldap_entries table. It controls whether this column is specified in the query that back-sql uses to map a DN to an entry in the ldap_entries table. In the sample tables provided with back-sql, the dn_ru column is either not present or contains no data.

  6. Use the .sql files provided with back-sql to create the sample tables that let you test the new back-end.

    The .sql files are located in the rdbms_depend/dbms subdirectory. To import the tables, do one of the following:

    • For Oracle, in SQL*Plus, execute each script by typing the following command at the SQL prompt:
      @sql_script

      replace sql_script with the .sql file path.

    • For SQL Server, execute each script in SQL Query Analyzer.

    To create the test tables, run backsql_create.sql. To create the test data and metadata run testdb_create.sql, testdb_data.sql, and testdb_metadata.sql scripts.

  7. As root, start slapd, the stand-alone LDAP server by running:
    /usr/local/libexec/slapd -d -1
  8. To test that you can retrieve the sample data from your backend database, use the LDAP client application ldapsearch. For example:
    ldapsearch -x -b dc=example,dc=com sn=Kovalev
    # extended LDIF
    #
    # LDAPv3
    # base <dc=example,dc=com> with scope subtree
    # filter: sn=Kovalev
    # requesting: ALL
    #
    
    # Mitya Kovalev, example.com
    dn: cn=Mitya Kovalev,dc=example,dc=com
    objectClass: inetOrgPerson
    cn: Mitya Kovalev
    sn: Kovalev
    givenName: Mitya
    userPassword:: bWl0
    telephoneNumber: 222-3234
    telephoneNumber: 332-2334
    
    # search result
    search: 2
    result: 0 Success
    
    # numResponses: 2
    # numEntries: 1

Notes

Resources

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)