t-sql query that queries Active Directory for list of users

Hi experts,

I'm using SQL Server 2019.

I need to create a SQL Server Stored Procedure that queries Active Directory.


Query 1

Windows Powershell query - Get Active Directory users whose password will expire in 15 days.

At this link I saw this query to for a Powershell Script that queries Active Directory and gets Active Directory users whose password will expire in 15 days.

List of users with paswords expiring within a certain date range
https://social.technet.microsoft.com/Forums/en-US/c9fb29c4-1b5b-459c-80cc-d6a83aeaf168/list-of-users-with-paswords-expiring-within-a-certain-date-range?forum=winserverDS



I replaced the domain with my domain info and then I run this power shell script.

Get-ADUser -filter * -SearchBase "OU=Users,DC=Contoso,DC=com"  -properties PasswordNeverExpires,msDS-UserPasswordExpiryTimeComputed | where {$_.enabled -eq $true -and $_.PasswordNeverExpires -eq  $False} | select Name,@{Name="ExpiryDate";Expression={([datetime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")).DateTime}} | where {($_.ExpiryDate | get-date)  -gt (get-date) -and ($_.ExpiryDate | get-date) -lt (get-date).adddays(15) }

Select allOpen in new window

I get this, which is the a list of users whose password will expired in 15 days or less.
So this powershell query returns the result I need. But I need to query Active Directory from a SQL Stored Procedure.



Query 2

So to learn how to query active directory from a sql server query. I looked at this reference which has the information on how to do it.

Querying Active Directory Data from SQL Server
https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

Step 1:      Create Linked Server
Step 2:      In the SQL query use LDAp information

After i created a linked server with the script in the link aboei, i then ran this query. But in my query that I ran, i replaced contoso with my domain ino.

SQL Server Query

SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 
  FROM  ''LDAP://Contoso.com/OU=Users,DC=Contoso,DC=com'' 
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDER BY displayname

Select allOpen in new window

I ran this sql server query in sql server management studio and it ran fine and it gave me similar result as this.




How would I revise my Sql Server query to give me the same result as the Powershell query shown above?
 I don't know the syntax.

The Sql Server query should return the Name and ExpiryDate of Active Directory users whose password will expire in 15 days