Project Summary
We have several web-based applications that require some sort application level database for managing user accounts and generating reports. Many of our web-based applications utilize a company's LDAP directory for single sign-on capability and updating records when users logon to the application. The benefits of this strategy is that applications can automatically recognize new employees to the organization and reject those that have been removed without intervention from the application administrator. The drawback to this strategy is that the application database only gets updated when user logs on to use the application. If the user does not logon to the tool or leaves the organization, the application level database can become out of sync with the current company structure. This can create discrepencies on reports generated from user profiles (Ex. Sales by cost center, department, etc.)

Solution
Sync2Ldap was created as a means to update the application database records with current information stored in a company's LDAP directory. The Sync2Ldap program is scheduled to run at reqular intervals (Windows schedule service or Unix cron job) to update one or more application databases.

Requirements

• Java 2 runtime environment (J2SE)
• JDBC driver for database that will be updated
• LDAP Directory to synchronize against

Tested Systems

• MySQL using MySQL jdbc driver (MySQL AB)
• Microsoft SQL Server 2000 using the Microsoft jdbc driver
• Microsoft SQL Server 7 using the jTDS open source driver

Program Usage
To run the program on a Windows machine, enter the following at the command prompt or within a batch file. Internally, we utilize a single batch file that runs the program using different configuration files for each database that needs updating. The batch file is then scheduled to run using the Windows Schedule Service.

	C:\MicroWeb\Sync2Ldap>java -jar Sync2Ldap.jar <config_file>

Configuration File Parameters

app.debug	[yes|no] : turns debug messages on/off
db.driver	The JDBC driver used to access the master database. Example (MySQL database): db.driver=com.mysql.jdbc.Driver
db.url	The JDBC driver connection paramters used to access the master database. Example (MySQL database): db.url=jdbc:mysql://127.0.0.1/mydatabase?user=myuser&password=mypassword
db.dateformat	The date format used by the master database. Example (MySQL database): db.dateformat=yyyy-MM-dd
ldap.host	The name or IP address of the LDAP directory server. Example: ldap.host=ldap.mydomain.com
ldap.base	The search base of where to search the LDAP directory server. Example: ldap.base=ou=people,o=mydomain.com
ldap.user	The user name required to search the LDAP directory server. If this is blank, an anonymous search will be performed.
ldap.pwd	The password required to search the LDAP directory server.
sync.table	The name of the table that will be synchronized. The table name must be the same on the master and remote databases. Example: sync.table=Employee
sync.keyfield	The name of the table primary key field. Example: sync.keyfield=hrid
sync.keymap	The name of the LDAP directory attribute that corresponds to the table primary key. This is used to match records in the database with those in the LDAP directory. Example: sync.keyfield=uid
sync.notfound	The name of the database field that will be set to a value of 1 if a matching record is NOT found in the LDAP directory. This is used to identify employees that have left the organization. Example: sync.notfound=isUnknown
sync.filter	The filter string used to determine which records to synchronize. By default, this is left blank to update all records in the table. Example: (synchronize records where the region field is 'EMEA'): sync.filter=Region='EMEA'
sync.field.N.name	Sync2Ldap can synchronize up to 100 table fields. N is a number from 0 to 99 that indicates a field to synchronize. Example: (synchronize six fields with the ldap directory) sync.field.0.name=FirstName sync.field.1.name=LastName sync.field.2.name=Handle sync.field.3.name=CostCenter sync.field.4.name=Email sync.field.5.name=Phone sync.field.6.name=Region sync.field.7.name= : : sync.field.99.name= Note: The numbers do not need to be in order. For example, to skip the Handle field above, remove the text after the = character as shown below. sync.field.2.name=

Sample Batch File (Sync2Ldap.bat)

# Run Sync2Ldap to synchronize application databases with Ldap directory
# Use java classpath option to tell system where file is located
# The Sync2Ldap.jar file is installed in the C:\MicroWeb\Sync2Ldap folder

cd C:\MicroWeb\Sync2Ldap
java -jar Sync2Ldap.jar SyncApp01.ini
java -jar Sync2Ldap.jar SyncApp02.ini
java -jar Sync2Ldap.jar SyncApp03.ini