For all / There exists

Introduction

This is a rather terse howto laying out a plan of attack for getting contacts from SugarCRM (www.sugarcrm.com) served out via LDAP without having to have anything like a cron job synching the two.

Requirements and Assumptions

This technique requires MySQL 5. This document assumes you're OK setting up things like permissions on your LDAP server. You need to decide now where you want it to be under your LDAP hierarchy. I'm going to use 'ou=contacts,dc=example,dc=com'.

Due to the restrictions on views, the email fields in the resulting LDAP will be read-only; the rest should be read-writeable. It is up to you to set the permissions correctlyon these fields as this will have to go with the rest of your security setup.

Don't be a muppet and just copy and paste from here; you'll need to replace things like 'sqluser', 'password' and 'ouname' with the choices you made for these values.

Create a database

You need to create a database to store the views and the metadata:

CREATE DATABASE ldap;
GRANT ALL privileges ON ldap.* TO 'sqluser'@'localhost' IDENTIFIED BY 'password';

Set up ODBC

Install ODBC and libmyodbc. Use odbcinst to install the mysql driver. I did:

odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini

but your mileage may vary depending on distribution. That line ought to work on a Debian system.

Then in /etc/odbc.ini set up the DSN. Mine looks like

[ldap]
DSN = ldap
Driver = MySQL
Description = LDAP SugarCRM contacts back-end DSN
Server = localhost
SOCKET = /var/run/mysql/mysql.sock
PORT = 3306
User = sqluser
Password = password
Database = ldap
Option = 3

To test, use

isql -v ldap

If you get an SQL> prompt, you're good to go. If you get an 'Unable to connect' error, check that the connection specified in odbc.ini is correct; some systems for example keep their mysql socket in /var/run/mysqld/mysqld.sock.

Setting up the Database

First of all, we'll create our metadata tables. You can get these from the /back-sql/rdbms_depend/mysql/backsql_create.sql file in the slapd source; this can be accessed on the web at http://www.openldap.org/devel/cvsweb.cgi/~checkout~/servers/slapd/back-sql/rdbms_depend/mysql/backsql_create.sql?rev=1.3&hideattic=1&sortbydate=0.

back_sql requires metadata to determine how to map our database onto our ldap schema. First of all we need to create a table to store our pretend OU.

CREATE TABLE ou (id INTEGER PRIMARY KEY AUTO_INCREMENT, ou VARCHAR(255));
INSERT INTO ou (1, 'ouname');

Now we can add a row to the metadata for the OU. As this is going to be read-only access we don't need to worry about the adding:

INSERT INTO ldap_oc_mappings (name, keytbl, keycol) VALUES ('organizationalUnit', 'ou', 'id');
INSERT INTO ldap_attr_mappings (id, oc_map_id, name, sel_expr, from_tbls) VALUES (1, 1, 'ou', 'ou', 'ou');

Now on to the contacts.

Unfortunately due to limitations of back_sql, it can deal only with integer keys - so we need to alter the SugarCRM contacts table slightly to provide an auto_increment field:

USE sugardb
ALTER TABLE contacts ADD COLUMN ldap_uid INTEGER UNIQUE NOT NULL AUTO_INCREMENT;
USE ldap;

SugarCRM's 'contacts' table provides two email addresses per contact; it would be nice to expose this as two LDAP email addresses. So create a view for it:

CREATE VIEW email_addrs AS (SELECT 1 AS id, ldap_uid AS uid, email1 AS email FROM sugardb.contacts) UNION (SELECT 2 AS id, ldap_uid AS uid, email2 AS email FROM sugardb.contacts)

For the rest of the fields, the mappings we want are:

SQL LDAP
CONCAT(first_name, ' ', last_name) cn
salutation personalTitle
first_name givenName
last_name sn
title title
phone_home homePhone
phone_mobile mobile
phone_work telephoneNumber
phone_fax facsimileTelephoneNumber
description description
primary_address_street street
primary_address_city l
primary_address_State st
primary_address_postalcode postalCode
primary_address_Country c

SQL	LDAP
CONCAT(first_name, ' ', last_name)	cn
salutation	personalTitle
first_name	givenName
last_name	sn
title	title
phone_home	homePhone
phone_mobile	mobile
phone_work	telephoneNumber
phone_fax	facsimileTelephoneNumber
description	description
primary_address_street	street
primary_address_city	l
primary_address_State	st
primary_address_postalcode	postalCode
primary_address_Country	c

For sanity and security, create a view of this:

CREATE VIEW contacts AS SELECT ldap_uid AS uid, CONCAT(first_name, ' ', last_name) AS cn, salutation AS personalTitle, first_name AS givenName, last_name AS sn, title, phone_home AS homePhone, phone_mobile AS mobile, phone_work AS telephoneNumber, phone_fax AS facsimileTelephoneNumber, description, primary_address_street AS street, primary_address_city AS l, primary_address_state AS st, primary_address_postalcode AS postalCode, primary_address_country AS c FROM sugardb.contacts WHERE deleted=0;

Add the metadata for the object class:

INSERT INTO ldap_oc_mappings (name, keytbl, keycol) VALUES ('inetOrgPerson', 'contacts', 'uid');

And for the fields:

INSERT INTO ldap_attr_mappings (oc_map_id, name, sel_expr, from_tbls) VALUES
(2, 'cn', 'cn', 'contacts'),
(2, 'personalTitle', 'personalTitle', 'contacts'),
(2, 'sn', 'sn', 'contacts'),
(2, 'givenName', 'givenName', 'contacts'),
(2, 'title', 'title', 'contacts'),
(2, 'homePhone', 'homePhone', 'contacts'),
(2, 'mobile', 'mobile', 'contacts'),
(2, 'telephoneNumber', 'telephoneNumber', 'contacts'),
(2, 'facsimileTelephoneNumber', 'facsimileTelephoneNumber', 'contacts'),
(2, 'description', 'description', 'contacts'),
(2, 'street', 'street', 'contacts'),
(2, 'l', 'l', 'contacts'),
(2, 'st', 'st', 'contacts'),
(2, 'postalCode', 'postalCode', 'contacts'),
(2, 'c', 'c', 'contacts');

Also for emails:

INSERT INTO ldap_attr_mappings (oc_map_id, name, sel_expr, from_tbls, join_where) VALUES (2, 'mail', 'email', 'email_addrs,contacts', 'email_addrs.uid = contacts.uid');

Now, create the view for ldap_entries. It must have the same form as the existing table.

CREATE VIEW ldap_entries AS (SELECT 1 AS id, 'ou=ouname,dc=example,dc=com' AS dn, 1 AS oc_map_id, 0 AS parent, 1 AS keyval) UNION (SELECT uid + 1 AS id, CONCAT('cn=', cn, ',ou=ouname,dc=example,dc=com') AS dn, 2 AS oc_map_id, 1 AS parent, uid AS keyval FROM contacts);

Setting up slapd.conf

This section assumes you already otherwise have ldap set up the way you like; in particular, it doesn't cover SSL/TLS or anything like that.

First, configure slapd to load back_sql. Do this by locating the line

moduleload back_dbd

and inserting after it

moduleload back_sql

If a database is a subtree of an existing one, it must be before it in slapd.conf; thus,before your bdb database section, you need to add the sql section:

database sql
suffix "ou=ouname,dc=example,dc=com"
rootdn "rootdn"
dbname ldap
dbuser sqluser
dbpass password
has_ldapinfo_dn_ru      no
concat_pattern "CONCAT(?,?)"
upper_func "UCASE"
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"

where rootdn is the same as the rootdn entry for your bdb database.

Please note that you should ensure you have adequate access control!

Once this is done you should be able to start slapd and have your SugarCRM contacts show up in LDAP.