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
I replaced the domain with my domain info and then I run this power shell script.
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
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
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) }
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
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
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.
ASKER
ste5an,
ok do you know the syntax to save a power shell query result into a table?
ok do you know the syntax to save a power shell query result into a table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks.
ASKER
great info. thanks.