Search This Blog and Web

Thursday, April 10, 2014

[TOOL] SQL Service Account password change/update (multiple servers)

Today, I'm going to share a tool that I've developed for changing password of SQL service account on (n) servers.

Background : Typically in an organisation as a best-practice account password is changed every 90 days(to prevent brute-force attack's success). Many Windows services are run using these accounts. Hence, there password needs to be updated.

Manual Way : Take a remote desktop connection to the server, open services.msc, select service and update password in Logon tab.

Src :
This is fine for 1 or 2 servers but this process does not scale-up in an enterprise where there are many servers and also many services are being run on each server(like sqlserveragent,ssis,ssas,ssrs).

Automation : As always, Power Shell is there to the rescue. There are two methods to achieve that 1. SMO 2. WMI
I first tried using SMO but it broke at crucial point. (Avoid SMO as far as you can.)
So, the second option with me was WMI.
Basically, all SQL services are Windows services and can be managed using Win32_Service class.

Basic Code :$service = gwmi win32_service -computer [computername] -filter "name='whatever'"
Full Code :

# Script should be run on systems having PowerShell V3 installed.
# Run PowerShell as ADMINISTRATOR.
# Remoting must be enabled on destination servers using "Enable-PSRemoting -Force"
# In case of errors, please try running this command on local machine : gwmi win32_bios -comp "server1" where "server1" is the machine for which you get error. This should run fine for utility to proceed.

    Service Account Password Change
    Power Shell utility to change password of SQL service account on (n) servers
    $servers : List of Server(s) separated by comma
    $AccountName : Name of the account through which service is running and whose password needs to be changed
    cd "E:\Utilities\ServiceAccount";
    .\SQLServiceAccount.ps1 -Servers "s1","s2" -accountName "redmond\v-any"
    Date : 15/01/2014
    Version : 1.0
    You must be having access to the servers.
    This should be run only from systems having PowerShell V3. Destination servers may have V2/V3.
    List of servers and account name
    Then password when prompted
    On Screen and file-logging.
    It logs into the directory from which it's invoked/called.

    # Seprate list by commas eg. "ser1","ser2"
    [string[]] $servers,
    # eg. "domain\account"
    [Parameter(Mandatory=$true)] [ValidatePattern("(\w+)\\(\w+)")]
    [string] $accountName

# Bail-out in case of any error
$ErrorActionPreference = "Stop"

# Generic helper function
function ConvertTo-ScriptBlock
    [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()]
    [string] $ScriptString
    $ScriptBlock = $ExecutionContext.InvokeCommand.NewScriptBlock($ScriptString)
    Return $ScriptBlock

function Get-ScriptDirectory
    # Look up this script file's path
    $Invocation = (Get-Variable MyInvocation -Scope 1).Value
    Split-Path $Invocation.MyCommand.Path

# This function writes the input Msg into the console window
function Log
    [string] $Msg = $(throw "Msg parameter not specified!")
    # Define the foreground color in which to log; if not specified, default provided
   ,[string] $Fore = "cyan"
    Write-Host (Get-Date -format "yyyy-MM-dd hh:mm:ss") $Msg `n`r -fore $Fore

Log "Account specified is $accountName"
# Get password from the user and store it as  a "Secure String"
# UserName, Message parameters are available only in V3
$pass = (Get-Credential -UserName $accountName -Message "Enter password for account")
If($pass -eq $null) {Log "Password not specified"; throw}

# get alias name from domain\alias e.g. domain\alias will return alias
$account = ($accountName.Split("\")[1])

$path = (Get-ScriptDirectory)
if(!(Test-Path $path)) {Log "Invalid log path $path" -Fore Red ; throw}

# Checks whether Transcript is in progress
function Test-Transcribing {
    $externalHost = $host.gettype().getproperty("ExternalHost",
        [reflection.bindingflags]"NonPublic,Instance").getvalue($host, @())

    try {
    [reflection.bindingflags]"NonPublic,Instance").getvalue($externalHost, @())
    } catch {
             Write-Warning "This host does not support transcription."

# The function that stops services (including dependent services), changes password & starts them again
function Change-ServiceAccount()
    # This is the code to fetch the services for SQL server including SSIS, OLAP
    $services = Get-WmiObject -Class win32_service -Filter "DisplayName LIKE '%SQL%'"
    Write-Host "Total SQL services are : " ($services.Count)`n`r
    foreach($service in $services)
        Write-Host "Processing service : " $service.Name`n`r

        # Need services that are run by the account specified
        if($service.StartName -match "$account")
        $serviceName = $service.Name
        Write-Host "Service is running under : " $service.StartName`n`r -ForegroundColor Yellow
        Log "Stopping service : $serviceName"
        # Stop All services so that "Dependent Services" are not running
        # It's better and simpler than $service.Stop() as the later does not stop dependent services by default

        Get-Service -Name $serviceName | Stop-Service -Force -Verbose

        Log "Changing password for $serviceName"

        # uint32 Change(DisplayName,PathName,ServiceType,ErrorControl,StartMode,DesktopInteract,StartName,StartPassword,LoadOrderGroup,LoadOrderGroupDependencies,ServiceDependencies);
        $RetCode = $service.Change($null,$null,$null,$null,$null,$null,$null,$pass)

        # Get error message from return code
        $msg = RetCodes -Code ($RetCode.ReturnValue)
        if($RetCode.ReturnValue -ne 0) { throw "Error : $msg" }

        Start-Sleep 2
        Log "Starting service : $serviceName"

        $RetCode = $service.StartService()

        $msg = RetCodes -Code ($RetCode.ReturnValue)
        if($RetCode.ReturnValue -ne 0) { throw "Error : $msg" }
            Write-Host "Skipping Service : " $service.Name `n`r -ForegroundColor White

# These are the list of return codes of Change() function which changes password
function RetCodes ([int] $Code)
    switch ($Code)
        0 { "Success" }
        1 { "Not Supported" }
        2 { "Access Denied"}
        3 { "Dependent Services Running"}
        4 { "Invalid Service Control"}
        5 { "Service Cannot Accept Control"}
        6 { "Service Not Active"}
        7 { "Service Request Timeout"}
        8 { "Unknown Failure"}
        9 { "Path Not Found"}
        10 { "Service Already Running"}
        11 { "Service Database Locked"}
        12 { "Service Dependency Deleted"}
        13 { "Service Dependency Failure"}
        14 { "Service Disabled"}
        15 { "Service Logon Failure"}
        16 { "Service Marked For Deletion"}
        17 { "Service No Thread"}
        18 { "Status Circular Dependency"}
        19 { "Status Duplicate Name"}
        20 { "Status Invalid Name"}
        21 { "Status Invalid Parameter"}
        22 { "Status Invalid Service Account"}
        23 { "Status Service Exists"}
        24 { "Service Already Paused"}
        default {"Invalid return code"}

# Make function calls from here
    # Read local function
   $RetFunction = (Get-Content Function:\RetCodes)
   $RetFunction = "Function RetCodes {" + $RetFunction + "}"
   $RetScriptBlock = (ConvertTo-ScriptBlock -ScriptString $RetFunction)

   $LogFunction = (Get-Content Function:\Log)
   $LogFunction = "Function Log {" + $LogFunction + "}"
   $LogScriptBlock = (ConvertTo-ScriptBlock -ScriptString $LogFunction)

    #Create log file to store execution result
    $StartTime = Get-Date -Format "yyyyMMdd_hhmm"
    $logFile = "$path\ServiceAccount_$StartTime.log"
    Start-Transcript $logFile

 foreach( $server in $servers)
   # Check Server is valid and alive
   Log "Testing Connection to $server"

   If(Test-Connection -ComputerName $server -Quiet)
        Log "Creating Connection to $server"
        $session = New-PSSession -ComputerName $server

        If($session.State -eq "Opened") { Log "Successfully connected to : $server"  -Fore "Yellow"}
        # Create function RetCodes in remote session
        Invoke-Command -Session $session -ScriptBlock $RetScriptBlock

        # Create function Log in remote session
        Invoke-Command -Session $session -ScriptBlock $LogScriptBlock

        # Pass local variable to remote session
        Invoke-Command -Session $session -ScriptBlock {param($account)} -ArgumentList $account
        Invoke-Command -Session $session -ScriptBlock {param($pass)} -ArgumentList $pass.GetNetworkCredential().Password

        # Call local function in remote session
        Invoke-Command -Session $session -ScriptBlock ${function:Change-ServiceAccount}
        Log "Server : $server is not valid/available, skipping it" -Fore "Red"


    Write-Host "oops! " $Error[0] `n`r -ForegroundColor Red
    Write-Host "Please check `"`" for error details"
    #Start-Process "" -ErrorAction SilentlyContinue

    Log "Clearing Sessions" -Fore "Yellow"
    Get-PSSession | Remove-PSSession
    if(Test-Transcribing) { Stop-Transcript }

Important Points : The above script is optimized using remoting and is amazingly fast. If you need a quick short version, only read and use Change-ServiceAccount function.

At the end, thanks to the all the people whose information I've used in creating this utility. Please let me know your comments on this.

Featured Post

Timeout problem in Backup-SQLDatabase cmdlet in SQLPS module

Power Shell is for managing all types of technologies be it SQL, EXchange, Lync, Office etc.( just name it ) These technologies usually cr...