LdapJdbc Driver

Introduction

The LdapJdbc driver allows reading LDAP directories via JDBC. Using SQL queries, data from an LDAP server can be extracted and processed just like from a database table.

LDAP ObjectClasses are addressed as tables for querying. Three components are used in the query:

an LDAP server is used as the database, addressed via a JDBC URL
the schema can be empty, public or system
the table is an LDAP ObjectClass (e.g. inetOrgPerson, groupOfNames)
the columns are LDAP attributes (e.g. cn, mail, telephoneNumber)

The driver uses JNDI (Java Naming and Directory Interface) for LDAP communication and the SQL parser from basejdbc for query processing.

JDBC URL Format

jdbc:ldap://server:port/baseDN     (unencrypted)
jdbc:ldaps://server:port/baseDN    (with SSL/TLS)

Examples

Unencrypted (LDAP on port 389):

jdbc:ldap://localhost:389/dc=example,dc=com
jdbc:ldap://ldap.myorg.de:389/o=myorg,c=de

Encrypted (LDAPS on port 636):

jdbc:ldaps://ldap.company.com:636/dc=company,dc=com
jdbc:ldaps://secure.example.com:636/ou=users,dc=example,dc=com

The URL consists of:

jdbc:ldap:// or jdbc:ldaps:// - Protocol (ldaps for encrypted connection)
server:port - The LDAP server and port
- Default: 389 for LDAP (unencrypted)
- Default: 636 for LDAPS (with SSL/TLS)
baseDN - The Base Distinguished Name from which the search starts

Connection Properties (Credentials)

The connection can be established with authentication.

Property	Description	Required
`bindDN`	The Distinguished Name (DN) for authentication	No (anonymous if not specified)
`password`	The password for authentication	No

Anonymous Access

When no credentials are specified, an anonymous LDAP bind occurs:

Connection conn = DriverManager.getConnection(
    "jdbc:ldap://localhost:389/dc=example,dc=com");

Authenticated Access

Properties props = new Properties();
props.setProperty("bindDN", "cn=admin,dc=example,dc=com");
props.setProperty("password", "secret");

Connection conn = DriverManager.getConnection(
    "jdbc:ldap://localhost:389/dc=example,dc=com", props);

Encrypted Access (LDAPS)

For encrypted connections use jdbc:ldaps:// and port 636:

Properties props = new Properties();
props.setProperty("bindDN", "cn=admin,dc=example,dc=com");
props.setProperty("password", "secret");

// Use ldaps:// and port 636 for encrypted connection
Connection conn = DriverManager.getConnection(
    "jdbc:ldaps://ldap.company.com:636/dc=company,dc=com", props);

Note: For LDAPS you may need additional Java configuration for SSL certificates:

// Optional: Configure SSL certificates
System.setProperty("javax.net.ssl.trustStore", "/path/to/truststore.jks");
System.setProperty("javax.net.ssl.trustStorePassword", "changeit");

SQL Syntax

Basic SELECT Query

SELECT dn, cn, mail, telephoneNumber
FROM inetOrgPerson

This searches the entire directory tree below the BaseDN for all entries with ObjectClass inetOrgPerson and returns the specified attributes.

FILTER Clause for LDAP Filters

The LdapJdbc driver uses the FILTER clause instead of WHERE for LDAP-specific filtering. The FILTER expression uses LDAP filter syntax:

SELECT dn, cn, mail
FROM inetOrgPerson
FILTER (mail=*@example.com)

LDAP Filter Examples

-- Exact match
FILTER (uid=jdoe)

-- Wildcard search
FILTER (cn=John*)

-- Multiple conditions (AND)
FILTER (&(mail=*@example.com)(ou=IT))

-- Multiple conditions (OR)
FILTER (|(title=Manager)(title=Director))

-- Negation
FILTER (!(employeeType=contractor))

Supported ObjectClasses (Tables)

The driver supports all LDAP ObjectClasses. Commonly used ones are:

ObjectClass	Description	Typical Attributes
`person`	Base person	dn, cn, sn
`organizationalPerson`	Organizational person	cn, sn, title, telephoneNumber, ou
`inetOrgPerson`	Internet organizational person	uid, cn, sn, mail, givenName, displayName
`groupOfNames`	Group	cn, member
`organizationalUnit`	Organizational unit	ou

Note: Table names (ObjectClasses) are case-insensitive and are automatically converted to lowercase.

Available Columns (LDAP Attributes)

The available columns depend on the ObjectClass. The driver:

Attempts to query the LDAP schema to determine all available attributes
Uses a fallback list with common attributes if the schema is not available
The dn column is always available and serves as the Primary Key

Common Attributes for inetOrgPerson

dn - Distinguished Name (always available, Primary Key)
cn - Common Name
sn - Surname
givenName - First name
mail - Email address
telephoneNumber - Phone number
uid - User ID
title - Title/position
displayName - Display name
ou - Organizational Unit

Multi-valued Attributes

LDAP attributes can have multiple values (e.g. several email addresses). The driver concatenates these with "; ":

mail = "john@example.com; john.doe@company.com"

System Tables

The LdapJdbc driver supports special system tables for metadata:

Retrieve Table List

SELECT table_name, table_type, remarks
FROM system.table_list
WHERE table_name LIKE '%person%'

Returns all available ObjectClasses (tables).

Retrieve Column List

SELECT column_name, type_name, remarks
FROM system.column_list
WHERE table_name = 'inetorgperson'

Returns all available attributes for an ObjectClass.

Retrieve Primary Keys

SELECT table_name, column_name
FROM system.primary_key_list
WHERE table_name = 'inetorgperson'

Returns the primary key columns (for LDAP always dn).

Examples

Example 1: List All Users

SELECT dn, cn, mail, telephoneNumber
FROM inetOrgPerson

Example 2: Filter Users by Email Domain

SELECT dn, cn, mail, title
FROM inetOrgPerson
FILTER (mail=*@example.com)

Example 3: All Groups and Their Members

SELECT dn, cn, member
FROM groupOfNames

The member attribute contains the DNs of the members, concatenated with "; " if multiple are present.

Example 4: Organizational Units

SELECT dn, ou
FROM organizationalUnit

Example 5: Users with Specific Role

SELECT dn, cn, mail, title
FROM inetOrgPerson
FILTER (title=*Manager*)

Example 6: Complex Filter

SELECT dn, cn, mail, telephoneNumber
FROM inetOrgPerson
FILTER (&(mail=*@example.com)(|(title=Developer)(title=Engineer)))

Searches for users with:

Email address at example.com AND
Title contains "Developer" OR "Engineer"

Conceptual Mapping

The conceptual mapping between LDAP and JDBC is as follows:

LDAP Concept	JDBC Concept	Description
LDAP Server	Database	The LDAP server contains the data
ObjectClass	Table	An ObjectClass defines the type of entries
Entry	Row	An LDAP entry corresponds to a row
Attributes	Column	LDAP attributes are columns
Distinguished Name (DN)	Primary Key	The DN uniquely identifies an entry
LDAP Filter	WHERE/FILTER	Filtering of entries

SQL Features

Supported Features

✅ SELECT statements
✅ Column selection
✅ LDAP filters via FILTER clause
✅ WHERE clause (processed by the framework, not passed to LDAP)
✅ System tables (system.table_list, system.column_list, system.primary_key_list)
✅ DatabaseMetaData functions

Technical Details

Implementation

The LdapJdbc driver uses:

JNDI (Java Naming and Directory Interface) for LDAP communication
No external LDAP SDK required (JNDI is part of the JRE)
Automatic schema detection from the LDAP server
Fallback to common attributes if schema is not available

Performance

Streaming: LDAP results are streamed, not fully loaded into memory
SUBTREE search: By default, the entire subtree is searched
Attribute filtering: Only requested attributes are retrieved from the LDAP server

Limitations

Read-Only: The driver supports only SELECT queries, no write operations
No sorting: LDAP returns results unsorted (ORDER BY not supported)
String output: All attributes are returned as VARCHAR
Multi-valued attributes: Are concatenated with "; "
Binary attributes: Are returned as string representation

LDAP Filter Syntax

The FILTER clause uses the standard LDAP filter syntax (RFC 4515):

Operators

Operator	Meaning	Example
`=`	Equality	`(cn=John Doe)`
`*`	Wildcard	`(cn=John*)`
`&`	AND	`(&(cn=John)(mail=@example.com))`
`	`	OR	`(	(title=Manager)(title=Director))`
`!`	Negation	`(!(ou=Sales))`
`>=`	Greater than or equal	`(uidNumber>=1000)`
`<=`	Less than or equal	`(uidNumber<=9999)`

Filter Examples

Simple filter:

FILTER (uid=jdoe)

Wildcard at end:

FILTER (cn=John*)

Wildcard at beginning:

FILTER (mail=*@example.com)

Wildcard in middle:

FILTER (cn=*Doe*)

AND combination:

FILTER (&(ou=IT)(title=*Developer*))

OR combination:

FILTER (|(ou=IT)(ou=Development))

Complex filter:

FILTER (&(objectClass=inetOrgPerson)(|(mail=*@example.com)(mail=*@company.com))(!(employeeType=inactive)))

Use Cases

Use Case 1: User Export

Export all active users from an LDAP directory:

SELECT dn, uid, cn, mail, telephoneNumber, title
FROM inetOrgPerson
FILTER (!(employeeStatus=inactive))

Use Case 2: Analyze Group Memberships

SELECT cn AS group_name, member
FROM groupOfNames

With further processing you can create a membership matrix.

Use Case 3: Map Organizational Structure

SELECT dn, ou
FROM organizationalUnit

Use Case 4: User Search for Self-Service

SELECT dn, cn, mail, telephoneNumber
FROM inetOrgPerson
FILTER (&(cn=*${searchterm}*)(!(employeeType=inactive)))

Replace ${searchterm} with the user input.

Use Case 5: Compliance Report

Find users without email address:

SELECT dn, cn, uid
FROM inetOrgPerson
FILTER (!(mail=*))

Integration with datasqill

The LdapJdbc driver can be seamlessly integrated into datasqill workflows:

Example: LDAP to Database Migration

Step 1: Query LDAP data

-- Source: LDAP
SELECT dn, uid, cn, mail, telephoneNumber
FROM inetOrgPerson
FILTER (ou=Marketing)

Step 2: Insert into target database

-- Target: PostgreSQL/MySQL/...
INSERT INTO users (ldap_dn, username, full_name, email, phone)
VALUES (?, ?, ?, ?, ?)

Example: LDAP Synchronization

Use the driver in datasqill to regularly synchronize LDAP data with a database.

Troubleshooting

Problem: "Unable to connect to LDAP server"

Cause: LDAP server not reachable or incorrect URL

Solution:

Check server and port: telnet ldap.example.com 389
Check URL syntax: jdbc:ldap://server:port/baseDN
Check firewall rules

Problem: "Undefined Column 'xyz'"

Cause: The ObjectClass does not contain the attribute in the schema

Solution:

Check available columns: SELECT * FROM system.column_list WHERE table_name = 'objectclass'
Use only attributes that are defined for the ObjectClass
Note that attribute names are case-insensitive

Problem: "No entries found"

Cause: Filter too restrictive or BaseDN incorrect

Solution:

Test without FILTER clause
Check the BaseDN in the URL
Check LDAP permissions

Problem: "Schema 'xyz' does not exist"

Cause: Wrong schema used

Solution:

Use system instead of xyz
Example: FROM system.table_list instead of FROM xyz.table_list

LdapJdbc Driver

Introduction

JDBC URL Format

Examples

Connection Properties (Credentials)

Anonymous Access

Authenticated Access

Encrypted Access (LDAPS)

SQL Syntax

Basic SELECT Query

FILTER Clause for LDAP Filters

LDAP Filter Examples

Supported ObjectClasses (Tables)

Available Columns (LDAP Attributes)

Common Attributes for inetOrgPerson

Multi-valued Attributes

System Tables

Retrieve Table List

Retrieve Column List

Retrieve Primary Keys

Examples

Example 1: List All Users

Example 2: Filter Users by Email Domain

Example 3: All Groups and Their Members

Example 4: Organizational Units

Example 5: Users with Specific Role

Example 6: Complex Filter

Conceptual Mapping

SQL Features

Supported Features

Technical Details

Implementation

Performance

Limitations

LDAP Filter Syntax

Operators

Filter Examples

Use Cases

Use Case 1: User Export

Use Case 2: Analyze Group Memberships

Use Case 3: Map Organizational Structure

Use Case 4: User Search for Self-Service

Use Case 5: Compliance Report

Integration with datasqill

Example: LDAP to Database Migration

Example: LDAP Synchronization

Troubleshooting

Problem: "Unable to connect to LDAP server"

Problem: "Undefined Column 'xyz'"

Problem: "No entries found"

Problem: "Schema 'xyz' does not exist"

Further Resources