Link to home
Start Free TrialLog in
Avatar of maqskywalker

asked on

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

User generated image
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) }

Open 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.

User generated image

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

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 ( 
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 
  FROM  ''LDAP://,DC=Contoso,DC=com'' 
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDER BY displayname

Open 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.

User generated image

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
Avatar of ste5an
Flag of Germany image

Do you really need to do it in T-SQL? I'd recommend to use a scheduled task to load the necessary data via PowerShell into a table and use this table.
Avatar of maqskywalker


ok do you know the syntax to save a power shell query result into a table?
Avatar of ste5an
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
great info. thanks.