Workaround to bypass the 1000 records limit, set by ADSI when querying Active Directory using SQL Server

1 Comment

Written by Luis Miguel – Originally published on January 29, 2011

Who can benefit from this post? – This post does not go into the details of how to build a solution to query Active Directory using SQL Server. Instead it presents a workaround for people that are already querying Active Directory using SQL Server and are hitting the 1000 records barrier imposed by ADSI.

As always, this is a short and sweet post so all the information needed to perform certain actions will not be included here. However the article should give you enough information to help you resolve the issue at hand fairly quickly.

Really QUICK-AND-DIRTY solution: If you have access to modify Active Directory settings, increase the value of MaxPageSize from its default of 1000 to something that suits your needs, BUT please read Avoid changing the MaxPageSize LDAP query policy to understand the possible consequences of this action, the author (unlike me) seems to know quite a bit about the topic.

For the rest of us – the ones that can only “view” Active Directory – read below.

The first time I had to work on a solution to query Active Directory using SQL Server I remember reading on the Internet a few posts talking about the 1000 records limit and at least one possible trick to bypass it. At the time it was not an issue for me, the AD implementation I was working with had less than 1000 users, so I didn’t pay attention to the specifics of the solution. I don’t know about you, but 99% percent of the time when I’m working on a project/task, there is barely enough time to get it out, let alone plan/build for “scalability”.

So here we are now with report consumers complaining about some user names missing from the “employee” list.

As I troubleshooted this issue, I noticed that there are a few user properties in AD that could break the dataset into chunks smaller than 1000 records, which I could then put together one at a time in a table and end up with the complete data set.

The property I choose to use was “whenCreated”, I could determine that if I added the filter “whenCreated < 20100101000000.0Z” to the WHERE clause of the query, Active Directory would return about 900 records. To get the rest of the data set (close to 300 records) I changed the query to whenCrated GREATER THAN 20100101000000.0Z

Here is a copy of the two complete queries:

INSERT INTO my_ad_users_list
SELECT givenname                  AS firstname,
       sn                         AS lastname,
       initials,
       displayname,
       samaccountname             AS ACCOUNT,
       telephonenumber,
       mobile,
       facsimiletelephonenumber   AS fax,
       pager,
       mail                       AS emailaddress,
       department,
       title,
       l                          AS city,
       postalcode,
       ipphone                    AS extension,
       physicaldeliveryofficename AS office,
       ‘my_domain’                AS domain,
       ‘regular’                  AS usertype
FROM   Openquery (adsi, ‘SELECT givenname, sn, initials, displayname, sAMAccountName, telephoneNumber,
 mobile, facsimileTelephoneNumber, pager, mail, department, title, l, PostalCode, ipPhone, physicalDeliveryOfficeName

  FROM ”LDAP://ad_server_name.my_domain.my_company.corp”

  WHERE userAccountControl=512 AND objectCategory = ”Person” AND objectClass = ”user”
 AND whenCreated < ''20100101000000.0Z'''
)
WHERE  givenname IS NOT NULL
       AND sn IS NOT NULL

INSERT INTO my_ad_users_list
SELECT givenname                  AS firstname,
       sn                         AS lastname,
       initials,
       displayname,
       samaccountname             AS ACCOUNT,
       telephonenumber,
       mobile,
       facsimiletelephonenumber   AS fax,
       pager,
       mail                       AS emailaddress,
       department,
       title,
       l                          AS city,
       postalcode,
       ipphone                    AS extension,
       physicaldeliveryofficename AS office,
       ‘my_domain’                AS domain,
       ‘regular’                  AS usertype
FROM   Openquery (adsi, ‘SELECT givenname, sn, initials, displayname, sAMAccountName, telephoneNumber,
 mobile, facsimileTelephoneNumber, pager, mail, department, title, l, PostalCode, ipPhone, physicalDeliveryOfficeName

  FROM ”LDAP://ad_server_name.my_domain.my_company.corp”

  WHERE userAccountControl=512 AND objectCategory = ”Person” AND objectClass = ”user”
 AND whenCreated > ”20100101000000.0Z”’
)
WHERE  givenname IS NOT NULL
       AND sn IS NOT NULL 

Tools used

-          SQL Server 2005

-          ADExplorer

One Comment (+add yours?)

  1. Jef
    Jan 29, 2011 @ 06:32:13

    It’s somewhat shocking to me that MS still has not fixed the ability to use openquery call the ADSI interface with the paging control some 11 years after Active Directory was released.

    Also, be careful with using “userAccountControl=512″ since this may not return all the users you expect. userAccountControl is a Bitmask attribute which may contain different variations of a “normal user” depending on what options might be set on the user. I assume you were looking for enabled users?

    Some background here: http://support.microsoft.com/kb/269181
    http://blogs.technet.com/b/heyscriptingguy/archive/2005/05/12/how-can-i-get-a-list-of-all-the-disabled-user-accounts-in-active-directory.aspx

    You might find this post also useful by using the CLR assemblies:

    http://blogs.msdn.com/b/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

    And here is an example using SQL language: http://stackoverflow.com/questions/1324361/sql-query-for-disabled-active-directory-accounts

    Good Luck!

    Jef

Powered by Netfirms