We help IT Professionals succeed at work.

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
Comment
Watch Question

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.

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.

Author

Commented:
thanks.

Author

Commented:
great info. thanks.