Find AD Accounts with expired passwords logging into M365

To my surprise, one day while working on a project to cleanup some Active Directory user accounts, I found some on-prem accounts with expired passwords logging into M365. This caught my attention since that meant that there were users who had not changed their password per the Password Policy but were still allowed to log into M365 and access resources without being forced to reset their password.

Now, I understand that “passwords” are going away. And I agree that “passwordless” is the future. But not everyone may be ready to make that move. Therefore, I needed a way to identify all ENABLED on-prem Active Directory accounts that had expired passwords and then find out which of those accounts were actively logging into M365.

So, I wrote a PowerShell script that does just that. The first part of the script lists ALL ENABLED AD Accounts with Expired passwords for your on-prem AD and creates a CSV file. The second part of the script uses this CSV as an INPUT file and queries the M365 tenant for last activity and creates a second CSV that identifies all the users that are NOT changing their passwords within the company Password Policy but are still accessing M365 recourses.

Pre-requisites
$DaysAgo – the variable for the # of days before passwords expire. This is different for every organization. For example, if your password Policy is to reset your passwords after 90 days, then set this variable to 91 days.

File Path – make sure to change the path for the CSV files to the proper path for you

PowerShell Modules – make sure you have the proper PowerShell AD and Microsoft Graph modules installed

The PS script creates two CSV files:
AccountWithExpiredPwds.csv – this file is used as an input file to query the M365 tenant for last activity
AccountsWithExpiredPwdsAndM365LastActivity.csv – this is the FINAL file which includes the M365 Last Activity information.

#Calculate date for Passwords older than xx days
$date = Get-Date
$DaysAgo = $date.AddDays(-xx)


#Export accounts with an Expired Password
Get-ADUser -Server "contoso.com" -Filter {Enabled -eq $true -and PasswordNeverExpires -eq $false} -Properties UserPrincipalName, msDS-UserPasswordExpiryTimeComputed, CanonicalName | 
Select-Object Name, SamAccountName, UserPrincipalName, Enabled, @{Name="PasswordExpiryDate";Expression={[datetime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")}}, 
@{Name="Domain";Expression={($_.CanonicalName -split '/')[0]}} | 
Where-Object { $_.PasswordExpiryDate -lt $DaysAgo } | 
Select-Object Name, SamAccountName, UserPrincipalName, Enabled, PasswordExpiryDate, Domain, @{Name="PasswordExpired";Expression={$true}} | 
Export-Csv -Path "C:\AccountsWithExpiredPwds.csv" -NoTypeInformation


# Read the file created above and pull in the M365 Last Activity data for each account and create a CSV File

Connect-MgGraph -Scopes "User.Read.All", "Reports.Read.All"

$users = Import-Csv -Path "C:\AccountsWithExpiredPwds.csv"
$results = @()

# Retrieve all users with their sign-in activity
$allUsers = Get-MgUser -All -Property "SignInActivity"

foreach ($user in $users) {
    $upn = $user.UserPrincipalName
    $userDetails = $allUsers | Where-Object { $_.UserPrincipalName -eq $upn }
    $lastSignInDate = $userDetails.SignInActivity.LastSignInDateTime

    $results += [PSCustomObject]@{
        Name                = $user.Name
        SamAccountName      = $user.SamAccountName
        UserPrincipalName   = $user.UserPrincipalName
        Enabled             = $user.Enabled
        PasswordExpiryDate  = $user.PasswordExpiryDate
        Domain              = $user.Domain
        PasswordExpired     = $user.PasswordExpired
        LastM365SignInDate  = $lastSignInDate
        
    }
}

$results | Export-Csv -Path "C:\AccountsWithExpiredPwdsAndM365LastActivity.csv" -NoTypeInformation

This will generate a nice little CSV of all ENABLED AD accounts that have an expired password and actively signing into M365. Once you have this information, you can begin planning for how to solve this issue. There could be several reasons why you may have user accounts that are not being forced to reset their passwords. One of them could be that the password expiration policy in your on-premises Active Directory (AD) does not automatically sync with Azure AD, which is used by Microsoft 365. If you are using AAD (Azure AD Connect) to sync your on-prem AD with Azure Entra ID, by default, Azure AD Connect does not sync the accountExpires attribute from on-prem AD to Azure AD. You would need to change the configuration of your AAD. But that is for another blog post.

I hope this helps someone. If so, leave a comment!