I had one of ‘those’ requirements the other day where I had a WebApp that needed to see all the Users information from Active Directory; however I also had to show details from the internal database. It’s one of the requirements that popup from time to time and has about 50 different options for implementation.
In this case I just wanted to have a nightly job to populate a simple table that I could query. Going direct to AD using code was to slow as there where about 20000 records and they where nested into 20 levels of folders.
The solution was to create a few simple stored procedures:
The first was to get all the Users from a provided folder name;
CREATE PROCEDURE [dbo].[GetUsersFromRoot] ( @root as varchar(1000), @name as varchar(100) = '' )
AS
SET NOCOUNT ON
-- get the
DECLARE @sqlString as varchar(1000)
SET @sqlString = 'SELECT '''+ @root+''' as OU, sn, givenName, lower(sAMAccountName), company, lower(userPrincipalName) FROM OPENQUERY(ADSI, ''SELECT givenName, sn, sAMAccountName, company, userPrincipalName FROM ''''LDAP://' + @root + ''''' WHERE objectClass=''''Person'''' AND objectClass=''''User'''' AND sAMAccountName='''''+@name+'*'''''')';
EXECUTE ( @sqlString )
;
The second was to get all the OU’s in the folder; this would be used for a recursive search.
CREATE PROCEDURE [dbo].[GetOUsFromRoot] ( @root as varchar(1000) )
AS
-- get the
DECLARE @sqlString as varchar(2000)
SET @sqlString = 'SELECT ''' + @root + ''' as RootOU, distinguishedName FROM OPENQUERY(ADSI, ''<LDAP://' + @root + '>;(&(objectClass=organizationalUnit));distinguishedName;onelevel'')';
EXECUTE ( @sqlString )
;
Now that we have these, we have something that will bind these all together…
CREATE PROCEDURE [dbo].[GetADUsers] ( @root as varchar(1000) )
AS
DECLARE @rootOUError as varchar(1000);
IF OBJECT_ID('tempdb..#ous') IS NOT NULL DROP TABLE #ouUsers
IF OBJECT_ID('tempdb..#ouUsers') IS NOT NULL DROP TABLE #ous
-- create user table if needed
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE Users ( OU varchar(1000), LastName varchar(100), Firstname varchar(100), LanId varchar(100), Company varchar(100), Email varchar(100)) ;
END
-- create temp tables
CREATE TABLE #ous ( RootOU varchar(1000), OU varchar(1000), error bit)
SELECT OU, LastName, Firstname, LanId, Company, Email INTO #ouUsers FROM dbo.Users WHERE 1=2; -- get round Collation issue
EXEC dbo.GetADUsersSub @root
-- recurse search any OU that had an error
DECLARE @ouErrorCursor CURSOR
SET @ouErrorCursor = CURSOR FAST_FORWARD FOR SELECT OU FROM #ous WHERE error=1 AND RootOU = @root
OPEN @ouErrorCursor
FETCH NEXT FROM @ouErrorCursor INTO @rootOUError
-- Get all users with the OU
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Error reported on ' + @rootOUError + ' so running against that OU.'
EXECUTE dbo.GetADUsersSub @rootOUError
FETCH NEXT FROM @ouErrorCursor INTO @rootOUError
END
;
GO
The procedure above will create our Temp table for the data and then kick off a recursive search for any folders within the root folder.
CREATE PROCEDURE [dbo].[GetADUsersSub] ( @root as varchar(1000) )
AS
SET NOCOUNT ON
------- Main procedure
DECLARE @alpha as int;
DECLARE @rootOU as varchar(1000);
DECLARE @ouUserCursor CURSOR
DECLARE @message as varchar(1000);
DECLARE @rootOUError as varchar(1000);
-- Get all OUs within this root
INSERT INTO #ous ( RootOU, OU ) EXECUTE dbo.GetOUsFromRoot @root;
SET @ouUserCursor = CURSOR FAST_FORWARD FOR SELECT OU FROM #ous WHERE RootOU = @root
OPEN @ouUserCursor
FETCH NEXT FROM @ouUserCursor INTO @rootOU
-- Get all users with the OU
WHILE @@FETCH_STATUS = 0
BEGIN
-- Loop over each character from A-Z as a simple way to Window the query
-- ADSI interface has a 900 entry limit which is a limitation to stop DOS attacks
-- increasing this limit is an option but for the moment this is a simple way to extract everything
SET @alpha = 65; -- start at 'A'
WHILE @alpha < 91
BEGIN
BEGIN TRY
DECLARE @character as char;
SET @character = char(@alpha);
INSERT INTO #ouUsers ( OU, LastName, firstname, lanid, company, email ) EXECUTE dbo.GetUsersFromRoot @rootOU, @character;
SET @alpha = @alpha+1
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
SET @message = ( SELECT ERROR_MESSAGE());
PRINT 'ERROR: getting OU data ' + @rootOU + '(' + @message + ')';
-- loog the error
UPDATE #ous set error=1 where OU = @rootOU;
BREAK;
END CATCH;
END
FETCH NEXT FROM @ouUserCursor INTO @rootOU
END
CLOSE @ouUserCursor
DEALLOCATE @ouUserCursor
-- Clean up the OU Names by removing anything without an email
UPDATE #ouUsers set OU = REPLACE(REPLACE(REPLACE(OU, @root, ''), 'OU=', ''),',','')
MERGE dbo.Users AS T
USING (SELECT DISTINCT OU, LastName, Firstname, LanId, Company, Email FROM #ouUsers WHERE LanID is not null and Email is not null) AS S
ON (T.LanId = S.LanId)
WHEN MATCHED THEN
UPDATE SET T.OU = S.OU, T.LastName = S.LastName, T.Firstname = S.Firstname, T.Company = S.Company, T.Email = S.Email
WHEN NOT MATCHED BY TARGET THEN
INSERT (OU, LastName, Firstname, LanId, Company, Email) VALUES (OU, LastName, Firstname, LanId, Company, Email);
PRINT 'Cleaning out Users Table';
TRUNCATE TABLE #ouUsers;
PRINT 'run the procedure for any OUs in error';
DECLARE @ouErrorCursor CURSOR
SET @ouErrorCursor = CURSOR FAST_FORWARD FOR SELECT OU FROM #ous WHERE error=1 AND RootOU = @root
OPEN @ouErrorCursor
FETCH NEXT FROM @ouErrorCursor INTO @rootOUError
-- Get all users with the OU
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Error reported on ' + @rootOUError + ' so running against that OU.'
EXECUTE dbo.GetADUsersSub @rootOUError
FETCH NEXT FROM @ouErrorCursor INTO @rootOUError
END
;
GO
Above; we get round the limits on AD queries by going via the alphabet, All A’s, B’s, C’s etc…. It’s not fast but it was easier than getting the network limits changed.
