Question
A customer wants to create collections based upon the security groups specified in Active Directory rather than OUs. How do you query Active Directory using LDAP via SQL?

Answer

How to Query Active Directory using LDAP in SQL Server 2000 / 2005

Step 1:- Add a linked server for the Active Directory

Syntax: exec sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5′, ‘ADsDSOObject’, ‘adsdatasource’

Example: exec sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5′, ‘ADsDSOObject’, ‘kenstest.krsdom.com’

Note: I had to use exec sp_dropserver ‘ADSI’, droplogins in order to readd it since ADSI already existed but with an incorrect datasource.

Step 2 :- You can query the Active Directory listing (LDAP is limited to only 1000 rows returned. You may need to adjust this) Refer to  the following Microsoft link for directions on how to change the maxpagesize. http://support.microsoft.com/kb/315071

Select * from OpenQuery(ADSI, ‘Select name from ''LDAP://kenstest.krsdom.com/DC=krsdom,DC=com’’ where objectClass =''Computer’’ and memberOf = ''CN=AdminSecurity,DC=krsdom,DC=com’’’)

Note1: All quotes in the query are single quotes.

Note2: Used DSQuery.exe to find proper syntax for  memberOf

Ran Dsquery * dc=krsdom, dc=com –attr * -limit 2000 >c:\ad.txt and then searched for AdminSecurity in ad.txt

Here is the list of columns that can be used to query.

List of columns for querying Active Directory using LDAP.

 Query that was created for Report and collection

SELECT vc.guid, vc.Name as [Computer Name],vc.[User],sn.[Computer Model],sn.[Computer Type] FROM vComputer as vc

      JOIN Inv_AeX_HW_Serial_Number as sn ON sn._ResourceGuid = vc.Guid

      WHERE Name IN

(SELECT * FROM OpenQuery(ADSI,

'Select Name from ''LDAP://kenstest.krsdom.com/DC=krsdom,DC=com''

where objectClass=''computer'' and

memberOf=''CN=AdminSecurity,DC=krsdom,DC=com''') as ad)

ORDER BY vc.Name