LDAP/Active Directory SQL-Like Queries

Depending on the data that is going to be read, SQL queries performed via SQLCreator or SQLExecutor often produce a better result than the LDAP Reader. Due to the ability to specify search scope, SELECT queries may look a little different from the average SQL.

Here is the basic structure (taken from https://myvd.sourceforge.net/bridge.html):

SELECT <*|DN|attribute1,attribute2,...,attributeX> FROM <scope>;<searchBase> [WHERE <whereClause>]

The first part denotes the attributes to be read:

  • DN – Returns Distinguished Name.
  • * – Returns all attributes of all nodes within the search scope. If your node contains various object classes with unrelated attributes, this will return them all.
  • attribute1,attribute2,... – List of attributes to be returned.

The second part denotes the search scope. Following are the valid values:

  • objectScope – Returns selected attributes only from the node from which you're searching.
  • oneLevelScope – Returns results from the node you’re searching from and its immediate children.
  • subTreeScope – Returns results from the node you’re searching from and all its descendants.

The third part is <searchBase> – The node that you’re searching from, in Distinguished Name format. Example: dc=domain,dc=com.

The fourth part is the WHERE clause. The WHERE clause uses SQL notation, not LDAP. Here’s how some LDAP filters can be represented using SQL notation:

Filter Type

LDAP Filter

SQL WHERE Clause

Equality

(cn=Some User)

... WHERE cn = 'Some User'

Partial

(cn=Marc*)

... WHERE cn LIKE 'Marc%'

Presence

(cn=*)

... WHERE cn IS NOT NULL

And

(&(cn=Some User)(sn=User))

... WHERE cn = 'Some User' AND sn = 'User'

Or

(|(cn=Some User)(sn=User))

... WHERE cn = 'Some User' OR sn = 'User'

Not

(!(sn=User))

... WHERE NOT sn = 'User'

Multi-Valued Attributes

In case any of the attributes are multi-valued, their values are concatenated into a single field using the pattern [value1][value2][value3].

Handling Large Result Sets

If you’re expecting a large result set to be returned by your searches, either via LDAP/ActiveDirectory Reader or by SQL queries, you may exceed the MaxPageSize policy in your LDAP server and run into a “Sizelimit exceeded” error message. To overcome this, you’ll need to increase the MaxPageSize policy in your LDAP server.

Here’s how to do this in an Active Directory server:

1. Log in as an LDAP admin in the domain controller, open Start menu > Run > ntdsutil.exe.
2. In the command prompt, under ntdsutil.exe, type LDAP policies and press Enter.
3. At the ldap policy command prompt, type connections.
4. At the server connections prompt, type connect to server <DNS name of server>.
5. Exit the server connections prompt by typing q.
6. Back on ldap policy command prompt, type show values to view the current ldap policy values.
7. From the ldap policy command prompt, type Set MaxPageSize to x, where x is the new desired limit.
8. To save changes, type commit changes.
9. To quit ldap policy, type q.
10. To quit NtdsUtil.exe, type q.