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

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

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

Step 2 :- Then follow the steps mentioned here for setting up permissions.

Step 3 :- You can query the Active Directory listing (It lists only 1000 records. LDAP design is like that)

SELECT * FROM
OpenQuery(ADSI,
‘SELECT displayName, sAMAccountName, givenName,
telephoneNumber, company,department,mail
FROM ”LDAP://your LDAP server url”
where objectCategory=”person” and objectClass=”user” and  sAMAccountName = ”your login name’ ”)

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

If you get “Could not convert the data value due to reasons other than sign mismatch or overflow.”  error message nothing to worry.

Its a known bug, and you can request patch from Microsoft.  Google with the error message or you can check it out here.

http://support.microsoft.com/kb/887474

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: