1

I have created below Powershell script to change sql server service accounts. This script is working fine from my local if i pass Standalone sql servers. But failing for clustered sql services with below error message:

$OldServiceAccount = '****'
$NewServiceAccount = '*****'
$Password = '****'
$Servers = Get-Content D:\RP-Test\Service_Account_Change\Servers.txt | Select-Object -Unique

foreach($Server in $Servers) {
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

    $wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $Server
    $Services = $wmi.services | Where-Object { ($_.ServiceAccount -eq $OldServiceAccount) -and ($_.Type -ne 'ReportServer') }

    foreach($Service in $Services) {
        $Service.SetServiceAccount($NewServiceAccount,$Password)
        $Service.Refresh()
    }
    $Services | Select-Object @{ label = "ServiceName"; expression = { $_.Name } }, Type, ServiceAccount, ServiceState | Format-Table -Auto
}

Error Message:

Exception calling "SetServiceAccount" with "2" argument(s): "Set service account failed. "
At line:3 char:27
+ $Service.SetServiceAccount <<<< ($NewServiceAccount,$Password)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

I am not able to change the Clustered sql service account remotely. If i copy this script to active node of the cluster and execute it there, then this script isworking fine.

BOL says

For Clustered servers: Changing the service account that is used by SQL Server or SQL Server Agent must be performed from the active node of the SQL Server cluster.(http://msdn.microsoft.com/en-us/library/ms345578.aspx)

Does this mean that we need to TS into each active node of the cluster and change service account. can't we automate this process? is this by desine? or is there any other way of changing service account for the clustered sql services.

sodawillow
  • 12,497
  • 4
  • 34
  • 44
Ram490
  • 69
  • 1
  • 1
  • 5
  • have you tested entering a remote pssession where the sql is and execute your script? – CB. Oct 12 '11 at 11:33
  • I copied the script to Active node of cluster and tried executing the script with below command. However this is working fine for the services which are not clustered(like SSIS service), but giving the exception for the Clustered services. Invoke-command -ComputerName Ramu-PC -ScriptBlock{D:\RP-Test\Service_Account_Change\DependentSVC.ps1} – Ram490 Oct 12 '11 at 13:08

0 Answers0