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 : http://www.sqlservercentral.com/blogs/steve_jones/2011/12/15/how-to-change-the-sql-server-service-cccount/ |
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'"
$service.change($null,$null,$null,$null,$null,$null,$null,"P@ssw0rd")
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.
<#
.SYNOPSIS
Service Account Password Change
.DESCRIPTION
Power Shell utility to change password of SQL service account on (n) servers
.PARAMETER Path
$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
.EXAMPLE
cd "E:\Utilities\ServiceAccount";
.\SQLServiceAccount.ps1 -Servers "s1","s2" -accountName "redmond\v-any"
.NOTES
Author: r1111111r@gmail.com
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.
.INPUTS
List of servers and account name
Then password when prompted
.OUTPUTS
On Screen and file-logging.
It logs into the directory from which it's invoked/called.
#>
param(
[cmdletBinding()]
# Seprate list by commas eg. "ser1","ser2"
[Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()]
[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 {
$externalHost.gettype().getproperty("IsTranscribing",
[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" }
}
else
{
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
try
{
# 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}
}
else
{
Log "Server : $server is not valid/available, skipping it" -Fore "Red"
}
}
}
catch
{
Write-Host "oops! " $Error[0] `n`r -ForegroundColor Red
Write-Host "Please check `"http://msdn.microsoft.com/en-us/library/aa384901(v=vs.85).aspx`" for error details"
#Start-Process "http://msdn.microsoft.com/en-us/library/aa384901(v=vs.85).aspx" -ErrorAction SilentlyContinue
}
finally
{
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.