Avatar of maqskywalker
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
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


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

PowershellQuery_Result.png

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

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.

sql server query result


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
Microsoft SQL ServerActive DirectorySQL

Avatar of undefined
Last Comment
maqskywalker

8/22/2022 - Mon
ste5an

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

ASKER
ste5an,
ok do you know the syntax to save a power shell query result into a table?
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
maqskywalker

ASKER
thanks.
Your help has saved me hundreds of hours of internet surfing.
fblack61
maqskywalker

ASKER
great info. thanks.