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:
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:
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
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
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.
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_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:
ALTER TABLE contacts ADD COLUMN ldap_uid INTEGER UNIQUE NOT NULL AUTO_INCREMENT;
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:
For the rest of the fields, the mappings we want are:
|CONCAT(first_name, ' ', last_name)||cn|
For sanity and security, create a view of this:
Add the metadata for the object class:
And for the fields:
(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:
Now, create the view for ldap_entries. It must have the same form as the existing table.
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
and inserting after it
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:
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.