openldap mysql documentation

October 31st, 2006

New effort as of Oct 2006

Made some very good progress:

  1. Installed slapd
  2. Imported the metadata database tables
  3. Fiddled with slapd.conf - you will have a database section which connects to the odbc driver using the database username and password. For example, if you are using mysql, the driver would likely be myodbc or myodbc3.
  4. Tested with:
ldapsearch -x

Metadata Database Tables

This is the most challenging part of the process for me, and I think a better understanding of ldap structures would help out immensely. You have to enter a bunch of terms like "inetorgperson" "organizationalUnit" and such, and there are rigid objects and attributes in ldap, like "o", "givenName", "name", etc. I like what I see, I just wish I was more familiar with it.


Flatmtn does a great job of explaining how to setup back-sql, however the document is outdated, and doesn't acknowledge that Mysql now supports views. So try this:

CREATE VIEW ldap_entries_test (id,dn,oc_map_id,parent,keyval) AS SELECT uid,CONCAT('uid=',uid,',ou=contacts,dc=docunext,dc=com'),2,1,uid FROM auth_user;

p>Test Steps

  1. Added "odbc" to /etc/make.conf
  2. Code:

host user # emerge -pv pam_ldap

These are the packages that I would merge, in order:

Calculating dependencies ...done![ebuild U ] sys-libs/db-4.2.52_p2-r1 [4.2.52_p2] -bootstrap -doc -java -nocxx -tcltk 3,980 kB [ebuild N ] dev-db/unixODBC-2.2.6 -gnome -qt 2,128 kB [ebuild N ] net-nds/openldap-2.2.28-r3 +berkdb +crypt -debug -gdbm -ipv6 -kerberos -minimal +odbc +perl -readline +samba -sasl (-selinux) -slp +ssl +tcpd 4,565 kB [ebuild N ] sys-auth/pam_ldap-180 +ssl 124 kB

Total size of downloads: 10,798 kB




author: TBONIUS

OpenLDAP is an X.500 Lightweight Directory Access Server used for centralized authentication and directory lookups. This article covers configuring this service to utilize SQL services in order to store its data object. Having these objects stored in a SQL database allow for third party applications access to manage these objects.


MySQL 4.x server : /usr/ports/databases/mysql41-server

MySQL 4.x client : /usr/ports/databases/mysql41-client

LibIODBC 3.x : /usr/ports/databases/libiodbc

MyODBC 3.x : /usr/ports/databases/myodbc

OpenLDAP 2.x : /usr/ports/databases/openldap21-server WITH_ODBC="YES"


OpenLDAP has the option to use many different kinds of databases, in this case we will use MySQL. The first step in setting this up is to create a MySQL database for which OpenLDAP will use.

root@host # mysqladmin create ldap

Next we will create a MySQL account that OpenLDAP will use for our newly created ldap database

root@host # mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>grant all privileges on ldap.* to 'ldap'@'localhost' ->identified by 'password' with grant option;

Query OK, 0 rows affected (0.13 sec)

We of course want to substitute 'password' with the actual password we wish to use for this particular user account


Quite simply we need to edit two file here to get LibODBC to use the MyODBC driver in accessing the MySQL server.

Take a look at the /usr/local/etc/libiodbc/odbcinst.ini file and make the following changes[ODBC Drivers]

MySQL = Installed[MySQL]

Description=ODBC for MySQL


Take a look at the /usr/local/etc/libiodbc/odbc.ini and make the following changes[ODBC Data Sources]

ldap = MySQL LDAP DSN[ldap]

Driver = /usr/local/lib/

Description = OpenLDAP Database

Host = localhost

ServerType = MySQL

Port = 3306

FetchBufferSize = 99

User = ldap

Password = password

Database = ldap

ReadOnly = no

Socket = /tmp/mysql.sock[ODBC]


Again, substitute password for the actual password we created for the ldap user of the MySQL database.

We can test our current configuration before installing and configuring OpenLDAP. LibIODBC provides a test utility to check DSN configurations.(Note from darxpryte: Upon following this tutorial I've found that iodbctest was not built automatically. This may be fixed later but if you find this to be the case you'll need to do the following:

cd /usr/ports/databases/libiodbc/

make extract

cd work/libiodbc-3.52.2/samples

make install This will install iodbctest into /usr/local/bin/)

Once you install iodbctest, you can do the following to test your connection:

root@host # iodbctest

iODBC Demonstration program

This program shows an interactive SQL processor

Driver Manager: 03.51.0001.0908

Enter ODBC connect string (? shows list): ?

DSN | Description---------------------------------------------------------------


Enter ODBC connect string (? shows list):DSN=ldap

Driver: 03.51.06

SQL>show tables;












result set 1 returned 10 rows.

This shows us that the DSN is configured correctly for LibIODBC to use the MyODBC driver in order to connect to our ldap database we set up on our MySQL Server

If you have problems displaying the DSN names defined in the odbc.ini file via the test program, try exporting the following shell environmental variable:

For csh or tcsh:

setenv ODBCINI /usr/local/etc/libiodbc/odbc.ini

For sh or bash:

export ODBCINI=/usr/local/etc/libiodbc/odbc.ini


During the build of OpenLDAP, we need to pass the WITH_ODBC="YES" option so that the server build the appropriate SQL configurations

After the make install process, we will copy over the slapd.conf file that is configured to use a SQL backend. This file is buried under the OpenLDAP ports directory in the following path:


Change to this directory, from the ports directory of OpenLDAP, and copy the configuration file over> cp slapd.conf /usr/local/etc/openldap

Then we can import the back SQL file from this directory into our running MySQL server database

root@host # mysql < backsql_create.sql ldap

root@host # mysql < testdb_create.sql ldap

Optionally we can import the testdb_data and testdb_metadata files into the database so that we can have example data with which to work

Next we need to edit the /usr/local/etc/openldap/slapd.conf file and make the protper adjustments. We need to setup the slapd service to use a SQL backend under the "SQL database definitions" section

database sql

suffix "o=sql,c=RU"

rootdn "cn=root,o=sql,c=RU"

rootpw secret

dbname ldap

dbuser ldap

dbpasswd password

subtree_cond "ldap_entries.dn LIKE CONCAT('%',?)"

insentry_query "INSERT INTO ldap_entries (dn,oc_map_id,parent,keyval) VALUES (?,?,?,?)"

Go ahead and comment out or delete any other example configurations for alternate SQL connectors such as Postgres and/or MsSQL settings. (Unless of course you are using a Postgres or MsSQL server as your backend


Next, we need to edit the /etc/rc.conf and configure the OpenLDAP server to star on boot by making the following changes


slapd_flags='-h "ldapi://%2fvar%2frun%2fopenldap%2fldapi/ ldap://"'


And finally we need to edit the OpenLDAP startup script and setup the ODBC path for the server to use. Edit /etc/rc.d/slapd file and add the following line:

export ODBCINI=/usr/local/etc/libiodbc/odbc.ini

Just as we performed the iodbctest, this variable is essential for OpenLDAP to know where the configuration file to use for ODBC connectivity

Now we are ready to try and bring up our OpenLDAP server. Let us start by running slapd manually in debug mode to see the output of startup:

root@host # /usr/local/libexec/slapd -d 1

We should see the following at the end of the debug output:<==load_schema_map()<==backsql_get_db_conn()==>backsql_free_db_conn()

backsql_free_db_conn(): closing db connection==>backsql_close_db_conn()<==backsql_close_db_conn()<==backsql_free_db_conn()<==backsql_db_open(): test succeeded, schema map loaded

slapd starting

If this is the given output then it looks like our configuration is correct and we are ready to start up OpenLDAP normally for operation./etc/rc.d/slapd start

This will startup the OpenLDAP server and we can verify it is running with the following command:

root@host # sockstat |grep slapd

ldap slapd 71838 5 dgram -< /var/run/log

ldap slapd 71838 8 stream /var/run/openldap/ldapi

ldap slapd 71838 9 tcp4 *:389 :

From here, use any OpenLDAP Administration tool of your choice to add, edit and remove data from your LDAP server

Yearly Indexes: 2003 2004 2006 2007 2008 2009 2010 2011 2012 2013 2015 2019 2020 2022