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
Filter | Description |
(employeeNumber=*) | Only if you use this on your organization otherwise leave it, in our case all users should have an employee number |
(objectCategory=person) | This means you are getting a person |
(objectClass=user) | This means you are getting a user class |
(userAccountControl:1.2.840.113556.1.4.803:=512) | NORMAL_ACCOUNT – This is a default account type that represents a typical user. |
(!userAccountControl:1.2.840.113556.1.4.803:=32) | PASSWD_NOTREQD – No password is required. With not operator we need all users with passwords only |
(!userAccountControl:1.2.840.113556.1.4.803:=2) | ACCOUNTDISABLE – The user account is disabled. With not operator we need all users that are only active |
(samAccountType=805306368) | The most efficient way to search for user objects on active directory, in most cases you will not need the objectCategory and objectClass filters. |
(accountExpires>= Current Date in Int8 format) | All accounts where expiry date is greater than today. |
(mail=*) | All users should have email |
(uSNChanged=*) | This is the update sequence number (USN) assigned by the local directory for the latest change, including creation, this filter eliminates few invalid accounts |
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.