We help IT Professionals succeed at work.

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

maqskywalker
maqskywalker asked
on
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
Comment
Watch Question

View Solution Only

ste5anSenior Developer

Commented:
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

Author

Commented:
ste5an,
ok do you know the syntax to save a power shell query result into a table?
Senior Developer
Commented:
Something like this:

#Requires -Version 5.0
#Requires -Modules SqlServer

Set-StrictMode -Version 2

Function ScriptMain() {
    $users = Get-ADUser -Filter * -SearchBase "OU=Users,DC=Contoso,DC=com" -Properties PasswordNeverExpires,msDS-UserPasswordExpiryTimeComputed |
        Where-Object -Filter {
            $_.enabled -Eq $true -And $_.PasswordNeverExpires -Eq  $False
        } |
        Select-Object Name, @{ Name="ExpiryDate"; Expression={ ([datetime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")).DateTime } } |
        Where-Object -Filter {
            ($_.ExpiryDate | Get-Date) -Gt (Get-Date) -And ($_.ExpiryDate | Get-Date) -Lt (Get-Date).AddDays(15)
        }

    ForEach ($user In $users) {
        $INSTANCE_NAME = 'SERVERNAME'
        $STATEMENT = 'INSERT INTO database.schema.table ( Name, ExpirationDate ) VALUES ( {0}, {1} );'
        $userName = Quote-String $user.Name
        $expirationDate = Format-Date $user.ExpiryDate
        Invoke-Sqlcmd  $($STATEMENT -f $userName, $expirationDate) -ErrorAction 'Stop' -MaxCharLength 65536 -ServerInstance $INSTANCE_NAME
    }
}

Function Format-Date {
    Param([[DateTime] $Date)
    "`'{0}`'" -f $Date.ToString('yyyy-MM-dd')
}

Function Quote-String {
    Param([String] $String)
    "`'{0}`'" -f $String.Replace("'", "''")
}

ScriptMain

Open in new window

The script must be run under a Windows user which has INSERT/CONNECT permissions to the database server (Windows authentication). Otherwise you need to lookup the syntax for Invoke-Sqlcmd.
maqskywalker

Author

Commented:
thanks.
maqskywalker

Author

Commented:
great info. thanks.