Change SQL Service Account Passwords without Restarting the SQL Service

Introduction

Since Secret Server cannot restart SQL services, you can use the PowerShell script below to change SQL service account passwords without restarting the SQL service.

Requirements

This script uses a privileged account so the secret requires an associated secret added to it. No custom template is required for this changer.

Procedure

Task 1: Adding the Script

1. Navigate to Admin | Scripts
2. Type or select the following:
   • Name: SQL Server Service Password Rotation
   • Description: Script for rotating SQL Server service account- no restart
   • Category: Dependency
   • Script: Copy and Paste the provided script below.
3. Click OK

Task 2: Creating the Dependency Changer

1. Navigate to Admin | Remote Password Changing

2. Navigate to Configure Dependency Changers

3. Click Create New Dependency Changer

4. Complete the form according to table below:

   Field	Value
   Type	PowerShell Script
   Scan Template	Windows Service
   Name	SQL Server Service Dependency Changer
   Description	SQL Server service rotation - no restart
   Port	Leave blank
   Wait(s)	Leave at 0
   Enabled	Leave checked
   Create Template	Leave checked

5. Click the Scripts tab

6. Click the Scripts drop-down, and select the PowerShell Script dependency changer you just created.

7. Type $MACHINE $SERVICENAME $PASSWORDin the Arguments field.

8. Click Save.

Task 3: Adding the Dependency to a Secret

1. Navigate to the desired Secret.
2. Navigate to Dependencies tab
3. Click New Dependency.
4. Click the Type dropdown list and select the dependency created in the previous step (it should under Standard section).
5. Click the Dependency Group dropdown list to select a current group or create a new one. If creating a new one, provide the New Group Name and New Group Site Name.
6. Obtain the service name using following command on the SQL Server target machine:
   Get-Service *sql* | Select-Object Name
7. Type the service name in the ServiceName field.
8. Select a Run As secret if needed.
9. Type the for the target machine name in the Machine Name field.

You should now be able to rotate the password of the SQL Server service account and verify the dependency was successful.

Script: sqlservice-norestart-dependency.ps1

<# Utilize the Run As secret #>
$params = $args
$Target = $params[0]
$ServiceName = $params[1]
$ServicePwd = ConvertTo-SecureString -String $params[2] -AsPlainText -Force

$serviceCred = [pscredential]::new('Ignore this value',$ServicePwd)
Invoke-Command -ComputerName $Target -ScriptBlock {
    [pscredential]$cred = $using:serviceCred
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')> $null
    $sqlwmiLocal = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'

    $targetService = $sqlwmiLocal.Services | Where-Object Name -EQ $using:ServiceName
    if ($targetService) {
        try {
            $targetService.ChangePassword('ignore this value',$cred.GetNetworkCredential().Password)
            $targetService.Alter()
        } catch {
            throw "Error updating the service password on $($using:Target) for service $($targetService.Name): $($_.Exception)"
        }
    } else {
        throw "Service $($using:ServiceName) not found on $($using:Target)."
    }
}

Troubleshooting

SQLWMI is part of SMO with SQL Server.

It exists on every installed SQL Server instance via the SQL Server Configuration Manager (SSCM). Newer versions of SQL Server no longer allow you to not install it. Just like WMI, there are cases where it can "break," most commonly when multiple versions of SQL Server are installed on the same machine—especially when a higher version was installed first, for example, if you installed SQL Server 2016 and then installed SQL Server 2012.

If running the dependency script you see the following error:

Failed to update <service name> on <target computer>. The following exception occurred while trying to enumerate the collection: "An exception occurred in SMO while trying to manage a service."

This can happen when SQLWMI is malfunctioning. Access that target and verify SSCM can be opened for that version of SQL Server you are trying to manage. If they have multiple versions, you need to open SSCM associated with the highest version found. If the target server is having issues, you will see WMI errors showing up when you open SSCM.

If this occurs you can solve the issue following this MS document: Error message when you open SSCM.