GetSmoObject Error: Attempt to retrieve data for object failed for ManagedCompter

Question

I am trying to write a PowerShell script that will enable the TCP/IP and Named Pipes Server Protocols, but my server is unable to find them. Here is the code I am using to connect to the protocols.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

$wmi = new-object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')
$uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()

The problem comes from the GetSmoObject cmdlet, which I get the following error:

When I run GetSmoObject with my $uri variable set to the following:

$uri = "ManagedComputer[@Name='<computer_name>']"

I get the following results:

My server isn't able to find the Server Instance or the Client Protocols, even though I know they exist.

Does anyone know how to setup my server so it can detect both Server Protocols that I need to update?

Answer 1

There are quite a few really good commands in this module from dbatools.

Hopefully this will get you started

client protocol settings: https://dbatools.io/functions/get-dbaclientprotocol/

server protocol settings: https://dbatools.io/functions/get-dbaserverprotocol/

Answer 2

Yea, use the "Microsoft.SqlServer.SqlWmiManagement" namespace:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

You'll create a new "machine" level object of type ManagedComputer. That will contain all the instances on that machine:

$machine = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList 'deezNutz'
$machine.ServerInstances.ServerProtocols | Select DisplayName, State, IsEnabled

Just keep in mind, ServerInstances is a collection of instances. If the machine hosts multiple instances, you'll need to reference the one you want. To keep it simple, I'm assuming the machine has a single default instance.

Now, if you want to disable to protocol. Just set the property to $False and bounce the instance.

$machine.ServerInstances.ServerProtocols.Item(0).IsEnabled = $True

You can cycle the instance via Restart-Service.