I have to change the service accounts of hundreds of SQL Servers and agents.
Microsoft recommends using the SQL Server configuration manager. This way works fine, but it is horrible if you have to do it hundred times.
On the more recent servers (Windows Server 2016, SQL Server 2016/2017) that PowerShell works fine:
$computer = $env:computername
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $computer
$wmi.Services | Select Name, DisplayName, Type, StartMode, ServiceState,
ServiceAccount | ft -auto
$sqlserver = $wmi.Services | where {$_.Type -eq "SqlServer"}
$sqlserver.SetServiceAccount($account, $pw)
But nearly every older server (Windows Server 2008-2012R2, SQL Server 2008-2014) gives me a empty WmiConnectionInfo an the exception:
ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services :
ClientProtocols :
ServerInstances :
ServerAliases :
Urn : ManagedComputer[@Name='SERVER']
Name : SERVER
Properties : {}
UserData :
State : Existing
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on SERVER.".
On the older server I checked the loaded assembylies. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo").FullName gives [...] Version=13.0.0.0 [...]. SQL Server 2014 is version 12, not 13.
But even if I load the right (?) versions of the dlls, the result stays the same.
[reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=...")
[reflection.assembly]::Load("Microsoft.SqlServer.SqlWmiManagement, Version=12.0.0.0, Culture=neutral, PublicKeyToken=...")
Do I have to load more assemblies with the correct version?
Is there another recommended way to change the SQL Server service accounts via PowerShell?
