Querying Active Directory / LDAP with SQL


Querying Active Directory or LDAP to obtain all user account is a little tedious, and the fact that you can only get 1000 records per query makes it a little difficult. Here is a script that will query AD for every combination of the three first letters of the account name, i.e. ‘AAA*’, ‘AAB*’, ‘ABA*’, ‘CAA*’…

Just remember to add the correct domain lame in the LDAP-query and you should be fine:

SET NOCOUNT ON

DECLARE @SearchTerm varchar(10)
DECLARE @RemoteQuery varchar(2000)
DECLARE @ErrCount int = 0
DECLARE @alphabet table (
Letter char(1)
)
INSERT INTO @alphabet (Letter)
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D'), ('E'),
('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')


IF OBJECT_ID('tempdb..#AD_Users') IS NOT NULL
DROP TABLE #AD_Users

CREATE TABLE #AD_Users (
givenName varchar(255),
initials varchar(255),
sn varchar(255),
userPrincipalName varchar(255),
sAMAccountName varchar(255),
displayName varchar(255),
name varchar(255),
physicalDeliveryOfficeName varchar(255),
telephoneNumber varchar(255),
mail varchar(255)
)

DECLARE cur CURSOR FOR
SELECT
CONCAT(char1.Letter, char2.Letter, char3.Letter, '*')
FROM @alphabet char1
CROSS JOIN @alphabet char2
CROSS JOIN @alphabet char3
WHERE char1.Letter >= 'A'

OPEN CUR

FETCH NEXT FROM cur INTO @SearchTerm
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @RemoteQuery = '
SELECT
givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM OPENQUERY( AD,
''SELECT givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM ''''LDAP://domain.name''''
WHERE objectCategory = ''''User''''
AND sAMAccountName = ''''' + @SearchTerm + ''''''')'


BEGIN TRY
INSERT INTO #AD_Users
EXEC ( @RemoteQuery )
END TRY
BEGIN CATCH
SET @ErrCount = @ErrCount + 1
END CATCH

FETCH NEXT FROM cur INTO @SearchTerm
END

CLOSE cur
DEALLOCATE cur


SELECT * FROM #AD_Users

SELECT @ErrCount


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s