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

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