`Update-DbaServiceAccount` - Cannot Find `ManagedComputer`

[mattcargile]

Verified issue does not already exist?

Yes

What error did you receive?

Cannot find type [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]: verify that the assembly containing this type is loaded.

Steps to Reproduce

Update-DbaServiceAccount -ComputerName $remote -ServiceCredential $cred -ServiceName SQLSERVERAGENT

Are you running the latest release?

Yes

Other details or mentions

Issue related to internal command Invoke-ManagedComputerCommand. On older servers with Microsoft Windows Server 2008 R2 Standard (e.g. 6.1.7601), it doesn't appear that loading the single assembly is enough. And probably maybe it has to do with the super duper aged SQL Server edition. :-/

https://github.com/dataplat/dbatools/blob/c62389edc648f26cd460348d344e55345282565f/internal/functions/Invoke-ManagedComputerCommand.ps1#L55-L56

Workaround

I added [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') above line 55.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe)

PowerShell Host Version

Local

[32;1mName Value[0m [32;1m---- -----[0m PSVersion 7.2.5 PSEdition Core GitCommitId 7.2.5 OS Microsoft Windows 10.0.19043 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

Remote

[32;1mName Value[0m [32;1m---- -----[0m PSRemotingProtocolVersion 2.3 CLRVersion 4.0.30319.42000 WSManStackVersion 3.0 PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSEdition Desktop BuildVersion 10.0.14409.1005 SerializationVersion 1.1.0.1 PSVersion 5.1.14409.1005

SQL Server Edition and Build number

Microsoft SQL Server 2005 - 9.00.5324.00 (Intel X86) 
	Aug 24 2012 14:24:46 
	Copyright (c) 1988-2005 Microsoft Corporation
	Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

.NET Framework Version

Local

.NET 6.0.6

Remote

.NET Framework 4.8.4110.0

[mattcargile]

Maybe my issue is something with Microsoft.SqlServer.SqlWmiManagement on Powershell Core. I'm getting some weird behavior on the command in this issue and Test-DbaSpn which both use the Invoke-ManagedComputerCommand.

[andreasjordan]

I will have a look while working on Test-DbaSpn.

[andreasjordan]

Can you retest with dbatools 2.0.1? If this is still an issue, I can have a look at it.

[mattcargile]

Thanks for following up, @andreasjordan. It appears in 2005 that dll didn't exist. I ran through the code below interactively because I don't have an environment now to test the full command.

https://github.com/dataplat/dbatools/blob/88dc002b38d22a6787e01ece8c4e9647a3e9f8d9/private/functions/Invoke-ManagedComputerCommand.ps1#L60-L73

So when we derive the $dll variable on the impacted machine it doesn't return a value. For the below code, I don't see the binary is loaded either.

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')
[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object {$_.Location }| Sort-Object -Property FullName | Select-Object -Property FullName, Location, GlobalAssemblyCache, IsFullyTrusted | ? { $_.fullname -like '*sql*' }

When I run the below, the assemblies are loaded and I can derive a ManagedComputer object. It loads C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll.

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object {$_.Location }| Sort-Object -Property FullName | Select-Object -Property FullName, Location, GlobalAssemblyCache, IsFullyTrusted | ? { $_.fullname -like '*sql*' }

After I do

$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ipaddr
$wmi
$wmi.services[0]

The below assemblies are loaded.

• C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
• C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
• C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoEnum\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.SmoEnum.dll
• C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
• C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll

All this to say I think on 2005, the SqlWmiManagement didn't exist and was inside Microsoft.SqlServer.Smo. Hopefully that is enough to make a decision one way or the other.

[andreasjordan]

So this is an issue only on very old and unsupported versions of Windows and SQL Server? In this case I don't want to make a change to the command that I can not test - as I don't have versions that old.

I would vote for closing this issue, but maybe @potatoqualitee wants to change the command...

[mattcargile]

That is fair. It may be nice to have a warning that the command is supported on SQL Server 2005?

[andreasjordan]

Ok, opened PR with documentation changes.

[mattcargile]

It would be nice if the command would just not run on 2005 so it would be more clear. I think we know it absolutely won't work on that version anyway.

[andreasjordan]

We just don't have information about the version. Or am I missing something?

[mattcargile]

So yeah, in this we wouldn't have the version of SQL Server unless we ran more code to check. Fortunately, I think we would only have this issue if Invoke-ManagedComputerCommand has to fail over and run on the server which I think has been resolved in v2.

There appears to be hacky check we can do from using $serviceObject.BinaryPath. For SQL Server 2005, the exe name for the Agent is SQLAgent90.exe instead of SQLAgent.exe.

https://github.com/dataplat/dbatools/blob/c455938c5a50f69a7db866c6429377eaf22d33c1/public/Update-DbaServiceAccount.ps1#L205

It looks like "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLAGENT90.EXE" -i MSSQLSERVER on my instance.

Bottom line is if the command fails on this error then do a check for 2005 SQL Instance and right an error that the command had to go remote and the proper libraries aren't available on the server.

[andreasjordan]

A lot ot work for an edge case (that I can not test). Will try to find time for that...

[andreasjordan]

We have more problems: As we use Invoke-ManagedComputerCommand, we can not catch all the exceptions as they are used in that command in a big try-catch-tree. I need more time for this...

[mattcargile]

A lot ot work for an edge case (that I can not test). Will try to find time for that...

Couldn't we use Connect-DbaInstance as a logic gate with the minimum version check?

[andreasjordan]

We are doing OS level stuff here and get an OS-credential for that. We potentially have no access to the sql server itself.

[mattcargile]

We are doing OS level stuff here and get an OS-credential for that. We potentially have no access to the sql server itself.

Yeah that is fair. Maybe just a check for the SQLWmi library and if it doesn't exist throw something about SQL Server 2005 and that it might not exist to avoid the null.

Maybe we can read the registry too but this is getting out of hand for this old version.

I have an old 2005 installer I may try to bring to life at home at some point.