ADO can also use SQL syntax to query Active Directory. This syntax is more familiar to some people, but many SQL features are not supported. SQL syntax uses the keywords SELECT and FROM. You can also use the keywords WHERE and even ORDER BY.

Both LDAP and SQL queries are strings that are assigned to properties of ADO objects. Like any VBScript string, the value is enclosed with double quotes. If you are using LDAP syntax, embedded string values in the query are not enclosed by quotes. For example, the following LDAP syntax query uses several string values, like "person" and "user":

"<LDAP://ou=Sales,dc=MyDomain,dc=com>;(objectCategory=person)(objectClass=user)" _

    & ";sAMAccountName,cn;Subtree"

SQL syntax requires that embedded strings be enclosed with single quotes. For example, the same query in SQL syntax would be:

"SELECT sAMAccountName,cn " _

    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _

    & "WHERE objectCategory='person' AND objectClass='user'"

Date values are also enclosed in single quotes, but numeric values are not. Note also that you must be careful to include spaces in the correct locations. Some more examples of SQL syntax queries follow:

To return sAMAccountName and distinguishedName of all objects of class "person" in the Sales Organizational Unit that are not members of any group (except their "primary" group):

"SELECT sAMAccountName, distinguishedName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE objectClass='person' AND NOT memberOf='*'"

To return sAMAccountName and distinguishedName of all users objects that do not expire in ou=Sales:

"SELECT sAMAccountName, distinguishedName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE objectCategory='person' " _
    & "AND objectClass='user' " _
    & "AND (accountExpires=0 OR accountExpires=9223372036854775807)"
 

To return sAMAccountName of all objects created after September 2, 2005, in ou=Sales:
"SELECT sAMAccountName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE createTimestamp>='20050902000000.0Z'"
 

To return all attributes of user j_smith in ou=Sales:
"SELECT * FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _

    & "WHERE sAMAccountName='j_smith'"
 

To return distinguishedName of all user objects in ou=Sales with a value of 546 assigned to the userAccountControl attribute:
"SELECT distinguishedName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE objectCategory='person' AND objectClass='user' " _
    & "AND userAccountControl=546"
 

To return distinguishedName of object in ou=Sales with GUID = "6394351061438F4B82662379F7C4408E":
"SELECT distinguishedName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _

    & "WHERE objectGuid='\63\94\35\10\61\43\8F\4B\82\66\23\79\F7\C4\40\8E'"
 

To return sAMAccountName of all objects in ou=Sales that are members of group TestGroup:
"SELECT sAMAccountName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE memberOf='cn=TestGroup,ou=West,dc=MyDomain,dc=com'"
 

To return distinguishedName and whenCreated for all users in ou=Sales created after September 1, 2006:
"SELECT distinguishedName, whenCreated " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE objectCategory='person' AND objectClass='user' " _
    & "AND whenCreated>='20060901000000.0Z'"
 

To return distinguishedName of all users in ou=Sales that have no value assigned to the description attribute:
"SELECT distinguishedName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE objectCategory='person' AND objectClass='user' " _
    & "AND NOT description ='*'"
 

To return the values of the distinguishedName, cn, sn, and givenName attributes of all user objects in ou=Sales sorted by cn:
"SELECT distinguishedName, cn, sn, GivenName " _
    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
    & "WHERE ObjectCategory='person' And ObjectClass='user' " _
    & "ORDER By cn"
 

An example using range limits would be:

SELECT 'member;range=0-999', sAMAccountName " _

    & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _

    & "WHERE ObjectCategory='group'"

Note in the previous example that the attribute name and range limits are enclosed by single quotes. There is no way known to test bits of the userAccountControl attribute using SQL syntax.

Send mail to HilltopLab@RLMueller.Net with questions or comments about this web site.
Copyright © 2002-2007 Richard L. Mueller
Last modified: June 13, 2008