SQL LDAP Query not returning all users

I am attempting to query my LDAP structure using the following SQL LDAP query and I am able to pull most all the users by samaccountname but I have a few users that the query is not finding.  I have verified using a LDAP Browser  to verify that the users do exist and the samaccountname is consist with all the other accounts.  Any ideas why this might be happening?
SELECT 
      sAMAccountName
      , ISNULL(mail,'') as mail
      , employeeId 
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, mail, employeeId FROM ''LDAP://OURSERVER/CN=Users,DC=ONE,DC=TWO,DC=THREE,DC=com''')
order by sAMAccountName

Open in new window

ittnvAsked:
Who is Participating?
 
St3veMaxConnect With a Mentor Commented:
File 1 is a Stored Proc which should go in a DB of your choice but not a system DB.
File 2 is the code to export the contents of AD into a table of your choice. You can populate this into a table. I've commented the insert bit out as I didnt have time to sort out an issue with a field length, but it's enough to give you the idea.

HTH

p.s. this query pulled over 4K rows from our AD.
1---Sync.usp-SetupOLE.txt
2---Export-from-AD.txt
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you get some 1000 records, and there are more, right?
well, LDAP is indeed limited to return 1000 records at once, so you have to get the data in chunks...
0
 
ittnvAuthor Commented:
Hmmm...That's interesting...but you let's say you have 1030 users and you run the query.  Would you only get a subset of the orginal 1030 or would you get the first 1000 entries in LDAP and then have to modify the query to get the next 30?  It appears that we are skipping users in our LDAP query rather than getting the first 1000?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
depending on what you specify as "first" :)

what I used is a loop to get all users A*, then B*, then C* etc ...
0
 
St3veMaxCommented:
There is an easier way. I'll pop a ZIP up in a moment.
0
 
ittnvAuthor Commented:
Thanks St3veMax for the code we'll take a look at it and see how it may help.  However we have discovered additional information related to the openquery ADSI link.  Since the link is used to access AD, permissions are in place to authenticate the user performing the query.  It appears as if our developer may be running into a permissions issue, so this leads to two questions.
1) does this sound accurate in reguards to AD and ADSI
2) where would I find how to modify such permissions
 
0
 
St3veMaxCommented:
I'm not sure if you will still be subject to the 1000 record limit...

Using the attached is very straight forward and doesent need any linked server.

HTH
0
 
ittnvAuthor Commented:
OK thanks to the code that St3veMax sent we found the problem.  It was not an issue with permissions it was an incorrectly structured query.  We have fixed the query and all is well.  Thanks to all for the help but St3ve get's the points for this one!
Corrected code is below:
select      
      Name
      ,Title
      ,TelephoneNumber
      ,sAMAccountName
      ,mail
      ,employeeId 
from  openquery(ADSI,
      'select     Name, Title, TelephoneNumber, sAMAccountName, mail, employeeId
       FROM ''LDAP://OURDCSERVER/CN=Users,DC=ONE,DC=TWO,DC=THREE,DC=com''
       where      objectClass = ''User''')
order by Name

Open in new window

0
 
ittnvAuthor Commented:
Nice code, we will use this for other queries as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.