The most comprehensive LDAP Query in SQL Server to Extract Active Users from Active Directory

If you are looking for the most complete LDAP Query in SQL Server to extract all your Active Directory Users then look no further this is the solution for you, in one query you can extract all the necessary users on your Active Directory using TSQL.

With this query you will be able to extract the right users who are active, not expired and valid users.  I will be giving two solutions one as a view so you can join it with a normal SQL Server table and another one will be as a stored procedure.

Two solutions give different result based on some limitations, like views cannot do complex logic like passing parameters on OpenQuery but is possible on stored procedure.  On Stored Procedure on the other hand you cannot join it to a table you use each solution based on your requirement.

Let start.

First we will be using a type of ADSI OpenQuery on this format

OPENQUERY(ADSI,''<LDAP://DC=com,DC=yourdomain>; .....

Rather than this format

OPENQUERY(ADSI, ''SELECT ..... FROM  '''LDAP://DC=com,DC=yourdomain''''

The reason being is so that we can use Normal LDAP Queries which have bitwise filters which are not possible though SQL.

Now that we have a base, lets discuss the filters that we will be using.  On this query here are the following filters that we will use

Now we have our filters lets build the queries, lets start with the view

CREATE VIEW [dbo].[ActiveDirectoryCompleteUsersList]
AS
SELECT
displayName,
sAMAccountName,
sn,
givenName,
title,
department,
mail,
ipPhone,
employeeNumber,
telephoneNumber,
mobile,
facsimileTelephoneNumber,
info,
accountExpires
FROM OPENQUERY(ADSI,
'<LDAP://DC=com,DC=yourdomain>;
(&(employeeNumber=*)(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=512)(!userAccountControl:1.2.840.113556.1.4.803:=32)(!userAccountControl:1.2.840.113556.1.4.803:=2)(samAccountType=805306368)(mail=*)(uSNChanged=*));
displayName,sAMAccountName,sn,givenName,title,department,mail,ipPhone,employeeNumber,telephoneNumber,mobile,facsimileTelephoneNumber,info,accountExpires,
cn,ADsPath;subtree')

Simple, as you can see we did not filter the accountExpires field since we cannot pass parameters to an OPENQUERY on a view but for stored procedure its a different story.

First we need a function to convert DateTime to Integer8 format since that is the date format used in Active Directory.  Meaning dates like this

2017-01-11 10:00:00.000 

will be converted to this

131286024720864000

So lets create that function first so we can use it on our stored procedure

CREATE FUNCTION [dbo].[ConvertDateTimeToInteger8] 
( 
 @DateToConvert DATETIME
) 
RETURNS NUMERIC(38,0) 
AS 
BEGIN

 --This is the number of nanoseconds in a day divided by 100 since Integer8 is using 100 nanosecond units
 DECLARE @NanoSecondsInADay BIGINT = 864000000000 
 
 --Variable to hold number of nanoseconds from the Beginning to get 100 nanosecond units
 DECLARE @NanoSecondsFromBeginning NUMERIC(18,0) 
 
 --Nanoseconds epoch dates 1601-01-01 and 1901-01-01 which is
 --299 Years * 365.242199 days in a year * 864000000000 (nanoseconds per day)
 DECLARE @NanoSecondsBetweenEpochDates NUMERIC(18,0) = 94355208720864000

 --Convert the Date Parameter to Big Int, which results to the number of days since 1901-01-01
 DECLARE @NumberOfDaysFromBeginning BIGINT = CONVERT(BIGINT, @DateToConvert)

 --Convert @NumberOfDaysFromBeginning to Nano Seconds, which result to NanoSeconds From Beginning
 SET @NanoSecondsFromBeginning = @NumberOfDaysFromBeginning * @NanoSecondsInADay

 --Need to add the NanoSeconds Epoch Dates since this is the discrepancy between SQL and Active Directory Dates
 RETURN @NanoSecondsFromBeginning + @NanoSecondsBetweenEpochDates
 
END
GO

Now that you have your function we are ready to go and use, lets now create our stored procedure

CREATE PROCEDURE GetActiveDirectoryCompleteUsersList
 
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @SQLQuery varchar(MAX)
 DECLARE @FloatDate varchar(50)
 SET @FloatDate = CONVERT(varchar, dbo.[ConvertDateTimeToInteger8](GETDATE()), 50)

SELECT @SQLQuery = 
 'SELECT
 displayName,
 sAMAccountName,
 sn,
 givenName,
 title,
 department,
 mail,
 ipPhone,
 employeeNumber,
 telephoneNumber,
 mobile,
 facsimileTelephoneNumber,
 info,
 accountExpires
 FROM OPENQUERY(ADSI,
 ''<LDAP://DC=com,DC=yourdomain>;
 (&(employeeNumber=*)(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=512)(!userAccountControl:1.2.840.113556.1.4.803:=2)(samAccountType=805306368)(accountExpires>=' + @FloatDate + ')(mail=*)(uSNChanged=*));
 displayName,sAMAccountName,sn,givenName,title,department,mail,ipPhone,employeeNumber,telephoneNumber,mobile,facsimileTelephoneNumber,info,accountExpires,
 cn,ADsPath;subtree'')'
 EXEC (@SQLQuery)
END
GO

Now I think you will ask this so I will mention, how about if your result set is more than 900? Well this is a real limitation but there are work around and for me what I did is filter it further by

SELECT ..... (sAMAccountName>=a*)(sAMAccountName<=i*) .... More Code Here .... 
UNION ALL 
SELECT ..... (sAMAccountName>=i*)(sAMAccountName<=r*) .... More Code Here .... 
UNION ALL 
SELECT ..... (sAMAccountName>=r*)(sAMAccountName<=z*) .... More Code Here ....

and then performing a union on all of my queries.  And yes the letters should overlap on less than and equal to greater than and equal, I found it when you just use greater than only the last letter on the equation is left out, must be a bug.

There you go, let me know if this can be further improved so we can add them here.