SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future

 

SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future

when I change sql agent service AD user password to meeting security requirement, I used the below powershell script and change all my 200+ sql instances sql agent service, no service restart required.

Today just found after I done a patching on one of my clusters, the sql agent service didn't start, I thought the password was old, and retype pwd, still failed to start, then I tried to reload the AD user by search user name in AD and type the password, it works.

script :

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

$text=Get-Content C:\sql\serverlist0.txt

$TotalComputers=$text.Length

write-host "the total number of computers is $TotalComputers"

For ($i=0; $i -lt $text.Length; $i++) {

    # write-host " the server name is $text[$i] being working on "

    $SMOWmiserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer’) $text[$i] #pull in the server you want 

    #$hostname=$text[$i]          

    $hostname=$SMOWmiserver.Name

    write-host "currently working on server with name as $hostname"

   # $SMOWmiserver.Services | Select-Object name, type, ServiceAccount, DisplayName, StartMode, StartupParameters | Format-Table

    $ChangeService=$SMOWmiserver.Services | where {$_.name -match '.*SQL.*AGENT.*'} #Make sure this is what you want changed!

#Check which service you have loaded first

    $ChangeService | select ServiceAccount, ProcessId, ServiceState,  StartMode, Name, State| Format-Table

 #$ChangeService

 

      $UName=”xxxxx”

     $PWord=”xxxxx”

    

  

      $ChangeService.SetServiceAccount($UName, $PWord)

 

    Clear-Variable -Name "ChangeService"

    }