Update-DbaServiceAccount not retrieving service from CIM object

[fuixx217]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Update-ServiceStatus] Failed to retrieve service name SQLSERVERAGENT from the CIM object collection - the service will not be processed

At line:151076 char:13
+             Get-RunspaceData -wait
+             ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Error], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Get-RunspaceData

Get-RunspaceData : Cannot bind argument to parameter 'InputObject' because it is null.
At line:151076 char:13
+             Get-RunspaceData -wait
+             ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Error], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Get-RunspaceData

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools

# user running this, or the initiating computer can be viewed as running on noncontoso.com, to indicate the need to specify the domain of the ComputerName as an FQDN
# the source machine with dbatools installed, may not have all connectivity necessary, as this is in a zero-trust environment with explicit ports opened up.  I can't tell if this is a domain short-name issue, or if I have a port issue.

# the error seems to occur when the targeted service is set, and the dependent services are attempted to be reviewed.

$ComputerName = "host fqdn where WMI ports are not open or something is amiss; myserver1.contoso.com"
$ServiceName = "MSSQLSERVER" # use the default instance name
$Domain = "contoso.com"

$target_service = Get-DbaService -ComputerName $ComputerName -ServiceName $ServiceName

foreach ($svc in $target_service) {$svc.ComputerName = "$($svc.ComputerName).$Domain";}

$ServiceAccountName = "CONTOSO\my_msa`$"

$target_service | Update-DbaServiceAccount -UserName $ServiceAccountName -EnableException

# please include variable values (redacted or fake if needed) for reference

Please confirm that you are running the most recent version of dbatools

Was a problem in both 2.0.4, and still occurs in 2.1.5

Other details or mentions

The targeted service seems to get set correctly. I have other code that winds up restarting the services again, and the SQL Server Agent is scooped up by that, but this seems like an issue that should be addressed.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.5122
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.5122
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU17) (KB5016394) - 15.0.4249.2 (X64) Jul 22 2022 12:11:33 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

.NET Framework Version

PSChildName Version

---

Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0

[fuixx217]

Full verbose and error output from the relevant section of my call to Update-DbaServiceAccount

VERBOSE: Completed Get-DbaService against <servername redacted><domain redacted> and service name MSSQLSERVER
VERBOSE: <servername redacted><domain redacted> Attempting to assign ONCOMCAST\svc_sqleng2429$ account to the service.  Should result in the service restarting...
VERBOSE: msa found with dollar sign or LocalSystem was used, running Update-DbaServiceAccount with no password
WARNING: [11:54:17][Set-DbaNetworkCertificate] New certificate will not take effect until SQL Server services are restarted for <servername redacted><domain redacted>
WARNING: [11:54:20][Get-DbaCmObject] [<servername redacted>] Unable to find a connection to the target system. Ensure the name is typed correctly, and the server allows any of
 the following protocols: CimRM, CimDCOM | Multiple protocol connections were attempted, but no successful connections could be established with the specified computer.
WARNING: [11:54:20][Update-ServiceStatus] Failed to retrieve service name SQLSERVERAGENT from the CIM object collection - the service will not be processed
Get-RunspaceData : Cannot bind argument to parameter 'InputObject' because it is null.
At line:97709 char:13
+             Get-RunspaceData -wait
+             ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Error], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Get-RunspaceData

Get-RunspaceData : Cannot bind argument to parameter 'InputObject' because it is null.
At line:97709 char:13
+             Get-RunspaceData -wait
+             ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-Error], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Get-RunspaceData

WARNING: [11:54:21][Update-ServiceStatus] (SQLSERVERAGENT on <servername redacted>) The attempt to start the service returned the following error: Unknown error.
WARNING: [11:54:22][Update-DbaServiceAccount] Failed to restart SQL Agent after changing credentials. The attempt to start the service returned the following error:
Unknown error.
ComputerName : <servername redacted>
ServiceName  : MSSQLSERVER
State        : Running
StartName    : <domain redacted>\<msa account name redacted>$
Status       : Successful
Message      : The login account for the service has been successfully set.

[mattcargile]

Interesting. My first thought was the destination SQL Server might have had multiple MSSQL versions installed on it causing it to not use the newest or correct library.

Now it just seems like general connectivity. Does Get-DbaCmObject -ClassName win32_service -computername $ServerName | select-object -first 10 work?

You could also do with running Get-DbaCmConnection after you run the commands to check that output on the $ServerName in question. Might be a decent troubleshooting position. You should probably run Test-DbaCmConnection $ServerName in a new session to see what you get as well.

[fuixx217]

I think, perhaps, the issue may be because the host where I am running the command is on contoso.com, for example, and the destination host is on contoso2.com

$ServerName = "myhost.contoso2.com" # note the fqdn Get-DbaCmObject -ClassName win32_service -computername $ServerName | select-object -first 10 works.
Get-DbaCmConnection -computername $ServerName also works

My guess is that somewhere in the Update-DbaServiceAccount function, the short name of the host is being passed to whatever should be populating the InputObject for the Get-RunspaceData call.

From my code above, the line $target_service = Get-DbaService -ComputerName $ComputerName -ServiceName $ServiceName returns a ComputerName property, but it is only the computer name, not the FQDN.

I think, as the looping is occurring, the resulting verbose output suggests it's trying to look at the dependent services for the $target_service, and while looping, it cannot make the connection to WMI with the short name because the short name exists on contoso2, but the session is running from contoso.

WARNING: [12:10:08][Update-ServiceStatus] Failed to retrieve service name SQLSERVERAGENT from the CIM object collection - the service will not be processed , if it had the server name output, I would guess it would lack the domain/fqdn.

[mattcargile]

It does look like it is using the FQDN from when I briefly looked at it.

https://github.com/dataplat/dbatools/blob/e6941ec910a81a6afc94a3f15170d09e5d7ff84f/public/Update-DbaServiceAccount.ps1#L174-L180

What does resolve-dbaNetworkName -ComputerName $serverName return for the FullComputerName?

Additionally, the below is the only code path I saw where Get-RunspaceData is called via Invoke-Parallel via Update-ServiceStatus via Start-DbaService.

https://github.com/dataplat/dbatools/blob/e6941ec910a81a6afc94a3f15170d09e5d7ff84f/public/Update-DbaServiceAccount.ps1#L270

Are you able to Stop-DbaService and Start-DbaService successfully on $ServerName?

[fuixx217]

What does resolve-dbaNetworkName -ComputerName $serverName return for the FullComputerName? This is returning the FQDN, as hoping.

Stop-DbaService is failing:

Stop-DbaService -ComputerName $ComputerName -InstanceName "MSSQLSERVER"

WARNING: [10:45:31][Get-DbaCmObject] [computershortname was here] Unable to find a connection to the target system. Ensure the name is typed correctly, and the server allows any of
the following protocols: CimRM, CimDCOM | Multiple protocol connections were attempted, but no successful connections could be established with the specified computer.

WARNING: [10:45:31][Update-ServiceStatus] Failed to retrieve service name MSSQLSERVER from the CIM object collection - the service will not be processed

WARNING: [10:45:31][Update-ServiceStatus] Failed to retrieve service name SQLSERVERAGENT from the CIM object collection - the service will not be processed

Get-RunspaceData : Cannot bind argument to parameter 'InputObject' because it is null.

[mattcargile]

I see. So then it is this line.

https://github.com/dataplat/dbatools/blob/e6941ec910a81a6afc94a3f15170d09e5d7ff84f/private/functions/Update-ServiceStatus.ps1#L193

I thought I traced that $group.Name to be the FQDN. I'll have to look again.

[fuixx217]

Seems like maybe line 117 of Stop-DbaService, and perhaps others, are pulling the InputObject using Get-DbaService, and then using the ComputerName value returned, which is the computer short name. Stop-DbaService line 117

PSComputerName returns the FQDN, but that doesn't seem like an ideal property to leverage, though it could work.

Stop-DbaService line 141 is passing $processArray along, which is really the Get-DbaService output.

Update-ServiceStatus line 188 references ComputerName for grouping, and PSComputerName could be the fix here.

Update-ServiceStatus line 193 would then have the fqdn of the PSComputerName value for the rest of the work.

[fuixx217]

@mattcargile Figured I would ping to see if there was any thought/review to my comment from a couple of weeks ago.

[fuixx217]

@mattcargile Pinging again

[mattcargile]

Sorry I couldn't ever get back to this. Looks like you got a good change in though. Thanks for sticking with it.