I am trying to automate the config of TCPPort number 1433 on SQL Server 2016 and 2019 with a PS script

Question

Inspired by this approach:

$pcName = $env:COMPUTERNAME
($dbmsName = Invoke-Sqlcmd -Query "SELECT @@servicename")

# Loading SQLPS environment
Import-Module SQLPS -DisableNameChecking -Force

# Initializing WMI object and Connect to the instance using SMO
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $pcName)

($uri = "ManagedComputer[@Name='$pcName']/ 
ServerInstance[@Name='$dbmsName']/ServerProtocol[@Name='Tcp']")

# Getting settings
($Tcp = $wmi.GetSmoObject($uri))

$Tcp.IsEnabled = $true

($Wmi.ClientProtocols)

# Setting IP properties
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="1433"

# Save properties
$Tcp.Alter()

# Restart service 
Restart-Service -Name MSSQL* -Force
Start-Sleep -s 30

Whenever I try to test with lastest version of PowerShell(5.1.17763.2090) on both SQL Servers 2016 and 2019: I get an error:

PS C:\Windows\system32> ($Tcp = $wmi.GetSmoObject($uri))
Exception calling "GetSmoObject" with "1" argument(s): "Attempt to retrieve data for object failed for ManagedComputer
'DB2T-30223'."
At line:1 char:2
+ ($Tcp = $wmi.GetSmoObject($uri))
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

What am I missing?

Answer 1

I found this on stack - Use PowerShell To Enable TCP/IP in SQL Server Configuration Manager

It seems like the same error place '+ ($Tcp = $wmi.GetSmoObject($uri))'

$pcName = $env:COMPUTERNAME
$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $pcName
$wmiinstance = $wmi.ServerInstances | Where-Object { $_.Name -eq $wmiinstancename }
$tcp = $wmiinstance.ServerProtocols | Where-Object { $_.DisplayName -eq 'TCP/IP' }
$IpAddress = $tcp.IpAddresses | where-object { $_.IpAddress -eq $IpAddress }
$tcpport = $IpAddress.IpAddressProperties | Where-Object { $_.Name -eq 'TcpPort' }