?
Solved

SQL LDAP Query not returning all users

Posted on 2008-11-10
9
Medium Priority
?
2,107 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:ittnv
  • 4
  • 3
  • 2
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926230
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
 

Author Comment

by:ittnv
ID: 22929925
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22929990
depending on what you specify as "first" :)

what I used is a loop to get all users A*, then B*, then C* etc ...
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 13

Expert Comment

by:St3veMax
ID: 22931052
There is an easier way. I'll pop a ZIP up in a moment.
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 2000 total points
ID: 22931147
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
 

Author Comment

by:ittnv
ID: 22939695
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
 
LVL 13

Expert Comment

by:St3veMax
ID: 22939825
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
 

Author Comment

by:ittnv
ID: 22939975
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
 

Author Closing Comment

by:ittnv
ID: 31515297
Nice code, we will use this for other queries as well.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question