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

book

Article ID: 181875

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

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.

1. countrycode
2. cn
3. msexchuseraccountcontrol
4. mailnickname
5. msexchhomeservername
6. msexchhidefromaddresslists
7. msexchalobjectversion
8. usncreated
9. objectguid
10. msexchrequireauthtosendto
11. whenchanged
12. memberof
13. accountexpires
14. displayname
15. primarygroupid
16. badpwdcount
17. objectclass
18. instancetype
19. msmqdigests
20. objectcategory
21. samaccounttype
22. whencreated
23. lastlogon
24. useraccountcontrol
25. msmqsigncertificates
26. samaccountname
27. userparameters

 

28. mail
29. msexchmailboxsecuritydescriptor
30. adspath
31. lockouttime
32. homemta
33. description
34. msexchmailboxguid
35. pwdlastset
36. logoncount
37. codepage
38. name
39. usnchanged
40. legacyexchangedn
41. proxyaddresses
42. userprincipalname
43. admincount
44. badpasswordtime
45. objectsid
46. msexchpoliciesincluded
47. mdbusedefaults
48. distinguishedname
49. showinaddressbook
50. givenname
51. textencodedoraddress
52. lastlogontimestamp
53. homemdb

 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