Update / Change Password via PowerShell

Today the password rotation for my Service Accounts in Active Directory (AWS MS AD) is due. It is a pain to have to create dozens of random passwords, then update Active Directory, store them in AWS Secret Manager and then update all of the apps.

To cut down on the time this takes, I created two PowerShell scripts and one SQL script to handle this.

AWS Secret Manager Updater

My first script will grab all the Service Account names from last month’s update in Secret Manager. I’ll then create new passwords for them and store that as a new Secret.

The script assumes I am running PowerShell 5.0+ and have AWS CLI set up with PowerShell on my host.

# PasswordGeneratorSolutionForAwsSecretManager
# PowerShell 5.0
# Update Passwords in MS AD on AWS.
# Written By David Speight
# December 8, 2022
# (1) This script will retrieve a previous months Service Account list ($LastMonthSecretListID) from AWs Secret Manager
# (2) The script will create new Passwords for the users
# (3) The script will save those passwords to a new Secret Manager Secret

# Define variable:  The SECRET Name of the previous months list of usernames and passwords, new month list name, description.
$LastMonthSecretListID = "sql-202304"
$ThisMonth = (Get-Date -Format "yyyyMM").ToString()
$NewMonthDescription  = "MS AD service accounts for SQL server {0}."  -f $ThisMonth
$NewMonthSecretListID  = "sql-{0}" -f $ThisMonth

$a = aws secretsmanager get-secret-value --region us-east-1 --secret-id $LastMonthSecretListID | ConvertFrom-Json 
$c = $a.SecretString 
$c = $c.Replace("{","")
$c = $c.Replace("}","")
$c = $c.Replace("""","")

#Password Generating Function
Function GeneratePassword
{
    $FirstAlpha =  ([char[]]([char]97..[char]122) | sort {Get-Random})[0] -join ''
    $SpecialChar = ([char[]]([char]40..[char]43) | sort {Get-Random})[0..1] -join ''
    $NumChar = (0..9 | sort {Get-Random})[0..1] -join ''
    $genpass = $FirstAlpha + (([char[]]([char]65..[char]90 ) + ([char[]]([char]40..[char]43) ) + ([char[]]([char]97..[char]126)) + 0..9 | sort {Get-Random})[0..7] -join '') 
    $genpass = $genpass + $SpecialChar + (([char[]]([char]65..[char]90 ) + ([char[]]([char]40..[char]43) ) + ([char[]]([char]97..[char]126)) + 0..9 | sort {Get-Random})[0..7] -join '') 
    $genpass = $genpass + $NumChar + (([char[]]([char]65..[char]90 ) + ([char[]]([char]40..[char]43) ) + ([char[]]([char]97..[char]126)) + 0..9 | sort {Get-Random})[0..7] -join '') 

    Write-Output $genpass
}


#Create New SECRET in AWS Secret Manager
$SecretJson = ""
$Addcomma = "N"

$d = $c.Split(",")
foreach ($item in $d) {
    $i = $item.IndexOf(":")
    
    # Call Generate Password Function
    $Password = GeneratePassword  

    #Add a comma at start of new line except on first iteration.
    if ($Addcomma -eq 'Y')
    {
        $SecretJson = $SecretJson + ','    
    }
    #Add Pair to Secret Manager json
    $SecretJson = $SecretJson + '\"' + $item.SubString(0,$i) + '\":\"' + $Password + '\"'
    $Addcomma = "Y"
}

#  Final format:      "{\"MyUser1\":\"MyPassword1\",\"MyUser2\":\"MyPassword2\"}"
$SecretJson = '{' +  $SecretJson + '}' 
Write-output $SecretJson

#COMMENT THIS LINE TO TEST and NOT  WRITE TO AWS
aws secretsmanager create-secret --name $NewMonthSecretListID --description $NewMonthDescription --secret-string $SecretJson

Next I log into the AWS Console and verify that the new SECRET has been created.

Active Directory Updater

Typically I give the developers a few days to obtain the new passwords before I actually run the update on Microsoft Active Directory. As you can see, the above script is dated four (4) days ago. So I am now going to run this next script to complete the AD update.

This script will retrieve the username and password for each of my Service Accounts, and update MSAD. The script also allows me to strip the prefix from a key value in the Secret. For example user MyDomain\Dave can be stripped down to just Dave. In the script I have to add an extra escape character that comes along with the json code. So I will strip “MyDomain\” away by entering “MyDomain\\” instead.

# PowerShell 5.0
# Update Passwords in MS AD on AWS.
# Written By David Speight
# December 12, 2022
# This script will change the user names in Active directory

# Start Script A:  The SECRET Name of the previous months list of usernames and passwords
$ThisMonth = (Get-Date -Format "yyyyMM").ToString()
$SecretListID = "sql-{0}" -f $ThisMonth
$DomainPrefixClean = "MyDomain\\"

$a = aws secretsmanager get-secret-value --region us-east-1 --secret-id $SecretListID | ConvertFrom-Json 
$c = $a.SecretString 
$c = $c.Replace("{","")
$c = $c.Replace("}","")
$c = $c.Replace("""","")



$d = $c.Split(",")
foreach ($item in $d) {
    $i = $item.IndexOf(":")
    $u = $item.SubString(0,$i)
    $u = $u.Replace($DomainPrefixClean,"")

    $user = $u
    $Password =  $item.SubString(($i + 1),($item.length - $i- 1))

    #Convert the password to secure string and pass to AD.
    $NewPwd = ConvertTo-SecureString $Password -AsPlainText -Force
    Set-ADAccountPassword $user -NewPassword $NewPwd -Reset
    Write-Host  $user, $Password
    $user = ""
    $Password = ""
}

Write-Host 
Write-Host "-----------------------------------"
Write-Host "------AUTH TEST ---------"
Write-Host "-----------------------------------"
Write-Host 

# AUTENTICATION TEST: This will query AD and verify the password was changed.
Function Test-ADAuthentication {
 param(
 $username,
 $password)
 
(New-Object DirectoryServices.DirectoryEntry "",$username,$password).psbase.name -ne $null
}


$d = $c.Split(",")
foreach ($item in $d) {
    $i = $item.IndexOf(":")
    $u = $item.SubString(0,$i)
    $u = $u.Replace($DomainPrefixClean,"")

    $UserName = $u
    $Password =  $item.SubString(($i + 1),($item.length - $i- 1))
    #Convert the password to secure string and pass to AD.
    Get-ADUser -Identity $UserName -properties PwdLastSet,PasswordLastSet  | sort Name | ft Name,@{Name='PwdLastSet';Expression={[DateTime]::FromFileTime($_.PwdLastSet)}},PasswordLastSet
    Test-ADAuthentication -username $UserName -password $password
    Write-Host "-----------------------------------"
    $UserName = ""
    $Password = ""
}

The results panel will skip over any users not found. I like this, since I also store SQL Logins in the same monthly file with my Domain users.

SQL Server Password Update

Lastly, this is the code I use to post the update for SQL Server User accounts. This one is manual.

USE [master];
ALTER LOGIN [ssis_user] WITH PASSWORD=N'MyNewPassword';
ALTER LOGIN [ssrs_user] WITH PASSWORD=N'MyNewPassword';