3/23/2011

Query Active Directory (AD) from SQL


We are attempting to query Active Directory through SQL to populate a SQL Table.  Here's what I've got so far (changed the IP address/table names).  It's a work in progress.  I've read about a limitation on returning # of rows per query, have not tackled that yet.
SELECT *
FROM OPENQUERY (ADSI,
      '
      SELECT
            mail, displayName
            ,userPrincipalName,sAMAccountName
            ,CN,givenName,SN,name,objectCategory
            ,company,department,manager
      FROM ''LDAP://168.123.45.67/DC=county,DC=local1''
      WHERE objectCategory=''person'' AND objectClass = ''user''
      ')
UNION
SELECT *
FROM OPENQUERY (ADSI,
      '
      SELECT

            mail, displayName
            ,userPrincipalName,sAMAccountName
            ,CN,givenName,SN,name,objectCategory
            ,company,department,manager

      FROM ''LDAP://168.123.45.67/DC=county,DC=local1''
      WHERE objectCategory=''group''
      ')
ORDER BY objectCategory

Enjoy!

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder