T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday

I’ve done one other of these in the past. This is a good one, and I too love using Powershell to automate management of multiple SQL Servers. Mine is an example of a situation I was in. We had 30 or so SQL Servers that were configured with Domain accounts to run each of the major services – the Engine, SSAS, SSIS, SSRS, and the Agent. However, these accounts were shared across the computers, so an account XYZ\SQLServer-SVC was used to run SQL Server Engine on all the servers. Due to circumstances outside my control we had to change passwords on all accounts.

I was dreading the fact that I would have to take a major outage and race to change the service account password in AD, and then do each server individually. Then I thought, POWERSHELL, you can save me! Using SMO.WMI.ManagedComputer was the trick for me. Here’s the code.

#Load the SqlWmiManagement assembly off of the DLL

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$serviceAccountList = Get-Content "SQL Servers.txt"

foreach ($server in $serviceAccountList)
    $SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server #Suck in the server you want
    $changeService = $SMOWmiserver.Services | where {$_.serviceAccount -eq "XYZ\SQLServer-SVC"} #| select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table
    if ($changeService.name -ne $null) {
        foreach ($service in $changeService)
            "Service " + $service.name + " found on " + $server
	    # Change the service account name or password as needed		

            "Service " + $service.name + " changed on " + $server
            $service = $null
    } else {
        "Specified account was not found on " + $server
    $changeService = $null
    $SMOWmiserver = $null

Right there, $service.setServiceAccount, that’s the magic. The nice thing about this approach is that it will take care of the dependencies. I started with another approach which would change the service account and then restart the service, but had issues with dependent services, like SQL Agent stopping me from stopping SQL Server outright. With the setServiceAccount part of ManagedComputer, it handled it for me.

Oh, and all new Servers, they have unique domain Service Account credentials, we no longer share a service account across computers. With SQL 2012, I plan on testing out Managed Service Accounts.