This script will change the startup account of the specified SQL Service account on the machine you specify.
[CmdletBinding()]
param(
[string]
[ValidateNotNullOrEmpty()]
$ServiceName,
[string]
[ValidateNotNullOrEmpty()]
$UserName,
[string]
[ValidateNotNullOrEmpty()]
$Password
)
[SecureString]$securePassword = ConvertTo-SecureString $Password -AsPlainText -Force
[PSCredential]$credentials = New-Object System.Management.Automation.PSCredential ($UserName, $securePassword)
$servers = Get-Content -Path (Join-Path -Path $PSScriptRoot -ChildPath 'servers.txt')
Invoke-Command -ComputerName $servers -ScriptBlock {
$credentials = $using:credentials
$service = $using:ServiceName
Write-Host ("Updating the '{0}' service logon credentials on '{1}.{2}' " -f $service, $Env:COMPUTERNAME, $Env:USERDNSDOMAIN.ToLower())
try
{
$ss = Get-CimInstance Win32_Service -Filter ('Name="{0}"' -f $service)
$ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName = $credentials.UserName; StartPassword = $credentials.GetNetworkCredential().Password }
}
catch
{
Write-Error ("An error occurred updating service logon creds on '{0}.{1}' " -f $Env:COMPUTERNAME, $Env:USERDNSDOMAIN.ToLower())
throw $_ | Format-List -Force | Out-String
}
$runningService = Get-WmiObject win32_service | Where-Object { $_.name -eq $service }
$runningService.StopService()
Start-Sleep -Seconds 8
$runningService.StartService()
}
# ------------------------------------------------------------------------------
<#
This script will change the startup account of the specified SQL Service account on
the machine you specify.
This script requires to be run in an elevated PowerShell session.
For Details on how to elevate your shell go to: http://bit.ly/anogNt
This script can be run to just tell you about the SQL services that you have running
on a given machine by highlighting until you get down to the first $ChangeService
Make sure to change: "MyServerName", "MSSQLSERVER"(if that's not the one you want),
"DomainName\UserName", "YourPassword"
This script may not work against a SQL Server that is not running PowerShell
(c) Aaron Nelson
Warning: I have not tested this with SSRS yet.
https://devblogs.microsoft.com/scripting/use-powershell-to-change-sql-server-service-accounts/
#>
#Load the SqlWmiManagement assembly off of the DLL
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | Out-Null
$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') 'WIN7NetBook' #Suck in the server you want
#These just act as some queries about the SQL Services on the machine you specified.
$SMOWmiserver.Services | Select-Object name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table
#Same information just pivot the data
$SMOWmiserver.Services | Select-Object name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List
#Specify the "Name" (from the query above) of the one service whose Service Account you want to change.
$ChangeService = $SMOWmiserver.Services | Where-Object {$_.name -eq 'MSSQLSERVER'} #Make sure this is what you want changed!
#Check which service you have loaded first
$ChangeService
$UName = 'DomainName\UserName'
$PWord = 'YourPassword'
$ChangeService.SetServiceAccount($UName, $PWord)
#Now take a look at it afterwards
$ChangeService
#To see what else you could do to that service run this: $ChangeService | gm
# ------------------------------------------------------------------------------
##
# Set Sql Server\Agent Service Account
#
# This uses a CIMMethod to change the service accounts.
# The Wmi.ManagedComputer method is also included in the comments below...
# but I've found this to be not reliable at times.
#
# https://sqlserverpowershell.com/2020/11/03/set-sql-serveragent-service-account/
##
[securestring]$mssqlpwd = ConvertTo-SecureString 'yomamma' -AsPlainText -Force
[PSCredential]$mssqlCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlserver", $mssqlpwd)
[securestring]$agentPwd = ConvertTo-SecureString 'wearsarmyboots' -AsPlainText -Force
[PSCredential]$agentCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlagent", $agentPwd)
Invoke-Command -ComputerName 'ComputerName' -ScriptBlock {
$mssqlCred = $using:mssqlCred
$agentCred = $using:agentCred
try
{
$ss = Get-CimInstance Win32_Service -Filter 'Name="sqlserveragent"'
$ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName = $agentCred.UserName; StartPassword = $agentCred.GetNetworkCredential().Password}
$ss = Get-CimInstance Win32_Service -Filter 'Name="mssqlserver"'
$ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName = $mssqlCred.UserName; StartPassword = $mssqlCred.GetNetworkCredential().Password}
<#
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
$service = $srv.Services | Where-Object{$_.name -eq 'sqlserveragent'}
$service.SetServiceAccount($agentCred.UserName, $agentCred.GetNetworkCredential().Password);
Restart-Service -name sqlserveragent -Force
$service = $srv.Services | Where-Object{$_.name -eq 'mssqlserver'}
$service.SetServiceAccount($mssqlCred.UserName, $mssqlCred.GetNetworkCredential().Password);
Restart-Service -name mssqlserver -Force
#>
}
catch
{
throw $_ | Format-List -Force | Out-String
}
Get-Service mssqlserver | Start-Service
Get-Service sqlserveragent | Start-Service
}
# ------------------------------------------------------------------------------
####################################
##
## Change SQL Server Service and Agent Account and Password
##
## If doing this for multiple server create a text file with the list of servers
## Additional functionality would need to be added if you want to also pass account name and password as variables but would be easy to add
## Output will be written to screen reporting success or failure of backup
##
## https://www.sqlservercentral.com/scripts/change-sql-server-service-accounts-with-powershell
##
####################################
## create server list from comma seperated list
##$serverlist = "<name of server>", "<name of server>", "<name of server>"
##server list from text file
$serverlist = Get-Content C:\serverlist.txt
foreach ($server in $serverlist)
{
Set-Location sqlserver:\sql\$server\default\JobServer\Jobs
##take a backup
## modify the Where-Object filter if an different naming convention is used for your backup job
$job = Get-ChildItem | Where-Object {$_.name -like '*backup*' -and $_.isenabled -eq 'true'}
$backup = $job.start()
Start-Sleep -s 5
##display current exectution status of backup job
do
{
'backup job running......'
$status = Get-ChildItem
$status.refresh()
$status = Get-ChildItem | Where-Object {$_.name -like '*backup*' -and $_.isenabled -eq 'true'}
Start-Sleep -s 2
} while ($status.CurrentRunStatus -contains 'executing')
## validate that backup completed enter yes to continue if backup failed enter No exit the process
Write-Host 'Backup Last run outcome ----> '$status.LastRunOutcome -BackgroundColor Blue -ForegroundColor Red
$response = Read-Host -Prompt "Did backupjob for '$($server)' successfully complete Y/N"
if ($response -eq 'Y')
{
##get sql service
$service = get-wmiObject win32_service -computername $server | Where-Object {$_.name -eq 'mssqlserver'}
$agent = get-wmiObject win32_service -computername $server | Where-Object {$_.name -eq 'SQLSERVERAGENT'}
# change sql service account and password. if acccount is local must put .\ in front of the account name
$changestatus = $service.change($null, $null, $null, $null, $null, $null, '<account name>', '<password>', $null, $null, $null)
$changeagent = $agent.change($null, $null, $null, $null, $null, $null, '<account name>', '<password>', $null, $null, $null)
## start-sleep added due to failures during testing. this seemed to resolve the failures
Start-Sleep -s 2
##error handling will be written to screen to validate if account change and service restart is successful
if ($changestatus.returnvalue -eq 0)
{
Write-Host $server'......service change successfull' -BackgroundColor Blue -ForegroundColor White
}
if ($changestatus.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'......service change successfull' -BackgroundColor red -ForegroundColor yellow
}
if ($changeagent.returnvalue -eq 0)
{
Write-Host $server'......Agent service change successfull' -BackgroundColor green -ForegroundColor White
}
if ($changeagent.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'......agent service change successfull' -BackgroundColor red -ForegroundColor yellow
}
## stop service after service account change
$stopagent = $agent.stopservice()
Start-Sleep -s 5
$stopservice = $service.stopservice()
## start-sleep added due to failures during testing. this seemed to resolve the failures
Start-Sleep -s 5
if ($stopservice.returnvalue -eq 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'.....Service stopped successfully' -BackgroundColor Blue -ForegroundColor White
}
if ($stopservice.returnvalue -gt 0)
{
Write-Host $server'....Service stopped unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
if ($stopagent.returnvalue -eq 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'.....Agent stopped successfully' -BackgroundColor green -ForegroundColor White
}
if ($stopagent.returnvalue -gt 0)
{
Write-Host $server'....Agent stopped unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
## start service account and account change will take effect
$startservice = $service.startservice()
if ($startservice.returnvalue -eq 0)
{
Write-Host $server'.....Service started successfully' -BackgroundColor Blue -ForegroundColor White
}
if ($startservice.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'.....service started unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
$startagent = $agent.startservice()
if ($startagent.returnvalue -eq 0)
{
Write-Host $server'.....agent started successfully' -BackgroundColor green -ForegroundColor White
}
if ($startagent.returnvalue -gt 0) ##if fails see what the return value is. this link describes the return values https://msdn.microsoft.com/en-us/library/windows/desktop/aa393660(v=vs.85).aspx
{
Write-Host $server'.....agent started unsuccessfully' -BackgroundColor red -ForegroundColor yellow
}
}
else
{
## if backup failed and No is entered process will bounce to the else
Write-Host 'please check why backup did not complete successfully' -BackgroundColor Red -ForegroundColor blue
}
}