Sometimes, you need to dump Active Directory data into a table or view in SQL Server. It is preferable to use a programming interface to do this or tools such as PowerShell. It is easier, less problematic, provides access to array values, and you don’t have to deal with the page limit. However, sometimes you need to do this from SQL Server.
The problem doing this with SQL Server (other than not having access to multi-value properties, dealing with bitwise operations in SQL, and dealing with fragile queries) is that you normally have a row/page limit set on Active Directory of 1000. This can be changed, but it is usually not an option.
Here I provide a SQL solution to get around the 1000 page limit. It generated a temp table that can be queried after retrieving the results from AD. It is not the most elegant, but it works fairly well in only about 20 lines of code. In this case, I’m retrieving all users. Note that a limitation to this query is having over 1000 users whose name (cn) start with the same letter. Usually this is not the case, but if that’s a problem this can be a good jump start.
DECLARE @SYMBOLS AS VARCHAR(128); DECLARE @query VARCHAR(MAX); DECLARE @ADfields VARCHAR(MAX);
SET @SYMBOLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -- AD Search character prefixes used to partition search. (1000 page limit). Below, AD fields to retrieve:
SET @ADfields = 'cn, sAMAccountName, accountExpires, pwdLastSet, userAccountControl, ADsPath, lockouttime, manager, mail, createTimeStamp, employeeID, lastLogon, co, l';
SET @query = 'SELECT * INTO ##tmpAD FROM (';
-- Get each character and for each character construct and AD query:
WITH nmTbl AS (SELECT TOP (LEN(@SYMBOLS)) Idx = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY Idx)
SELECT @query = @query + 'SELECT * FROM OPENQUERY(ADSI, ''SELECT ' + @ADfields + ' FROM ''''LDAP://DC=newfield,DC=com''''
WHERE objectCategory=''''Person'''' AND (cn = ''''' + SUBSTRING(@SYMBOLS, nmTbl.Idx, 1) + '*'''') AND (objectClass = ''''user'''' OR objectClass = ''''contact'''')'')
UNION
' FROM nmTbl;
-- Finish generating query string:
SELECT @query = LEFT(@query, LEN(@query) - CHARINDEX(REVERSE('UNION'), REVERSE(@query)) - 4) + ') AS qry'
-- Remove temp table if existing before running:
IF OBJECT_ID('tempdb.dbo.##tmpAD', 'U') IS NOT NULL
DROP TABLE ##tmpAD;
EXECUTE(@query)
SELECT * FROM ##tmpAD