Download SQL Server and Oracle Drivers for OpenLDAP. Free Trial.

Using OpenLDAP with Microsoft SQL Server and Oracle back-end 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 SQL Server ODBC driver with OpenLDAP and the SQL back end, back-sql, you can expose data in Microsoft SQL Server databases to LDAP client applications. By using the Easysoft Oracle ODBC driver, you can expose data in Oracle databases to LDAP client applications.

The SQL back end

A back end defines the type of database OpenLDAP uses to store or generate data. The standard back end, back-bdb, uses Oracle Berkeley DB to store data. The SQL back end, 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 SQL Server ODBC driver and Easysoft Oracle ODBC 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 SQL Server ODBC driver or the Easysoft Oracle ODBC 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, refer to the slapd-sql(5) man page and the OpenLDAP FAQ The SQL back end (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.

Download SQL Server and Oracle Drivers for OpenLDAP. Free Trial.

Installing the Easysoft ODBC driver

Installing the Easysoft SQL Server ODBC driver

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

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

    For installation instructions, refer to the Easysoft SQL Server ODBC driver documentation.

  3. Refer to the Easysoft SQL Server ODBC driver documentation for information about the necessary environment variables you need to set for the Easysoft SQL Server ODBC 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 SQL Server ODBC driver with isql.
    • Make and install the OpenLDAP distribution.
    • Start slapd.
  4. Create an Easysoft SQL Server ODBC 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 SQL Server ODBC driver data source configuration, refer to the Easysoft SQL Server ODBC 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, enter 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 provide an alternative way to access SQL Server from OpenLDAP. To install the ODBC-ODBC Bridge, follow these steps:

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

    For installation instructions, refer to the ODBC-ODBC Bridge documentation.

  3. Refer to the ODBC-ODBC Bridge documentation for information about the necessary environment variables you need to set for the ODBC-ODBC Bridge (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 ODBC-ODBC Bridge 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 ODBC-ODBC Bridge 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 ODBC-ODBC Bridge 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 user name 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_user_name
LogonAuth               = my_windows_password
TargetDSN               = sql_server_system_dsn

    For more information about data source configuration, refer to the ODBC-ODBC Bridge 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_user_name sql_server_password

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

Installing the Easysoft Oracle ODBC driver

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

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

    For installation instructions, refer to the Easysoft Oracle ODBC driver documentation.

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

  3. Refer to the Easysoft Oracle ODBC driver documentation for information about the necessary environment variables you need to set for the Easysoft Oracle ODBC driver (LD_LIBRARY_PATH, ORACLE_HOME, for example). You need to ensure that these variables are in your environment when you:
    • Test the Easysoft Oracle ODBC driver with isql.
    • Make and install the OpenLDAP distribution.
    • Start slapd.
  4. Create an Easysoft Oracle ODBC 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 Oracle ODBC driver data source configuration, refer to the Easysoft Oracle ODBC 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_user_name my_oracle_password

    where my_oracle_user_name and my_oracle_password are a valid Oracle database user name and password. At the prompt, enter 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. 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 back end.

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 SQL Server ODBC 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 ODBC-ODBC Bridge 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 back end, 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
Download SQL Server and Oracle Drivers for OpenLDAP. Free Trial.

Configuring and testing the SQL back end

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 back end that specifies the ODBC data source used to access the database and configures the default behaviour of the back end 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:
    Setting Value
    dbname The name of the Easysoft ODBC driver data source that you configured when you installed the Easysoft ODBC driver.
    dbuser A valid SQL Server or Oracle database user name.
    dbpasswd The 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 back end 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 entering 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 back-end 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

Download SQL Server and Oracle Drivers for OpenLDAP. Free Trial.