'Enable SQL Server protocol using pwsh
I want to enable server protocols using pwsh (not powershell):
I tried this script to enable SQL Server TCP/IP protocol in pwsh:
$cm = Get-CimInstance -Namespace 'root\Microsoft\SqlServer' -ClassName "__NAMESPACE" | ? name -match 'ComputerManagement' | Select-Object -Expand name
$cnp = Get-CimInstance -Namespace "root\Microsoft\SqlServer\$cm" -ClassName ServerNetworkProtocol
$tcp = $cnp | ? ProtocolDisplayName -eq 'TCP/IP'
$tcp.Enabled = $true
I get this error:
SetValueException:
Line |
5 | $tcp.Enabled = $true
| ~~~~~~~~~~~~~~~~~~~~
| "Enabled" is a ReadOnly property.
How can I do this?
I currently use this but it doesn't work in pwsh as assembly System.Management.Automation.PSSnapIn can't be loaded in it.
This might be handy but I didn't find a way to use it.
== EDIT ==
Thanks to @AlwaysLearning, this works in both pwsh and PowerShell (from admin shell):
function Get-SqlServerProtocol($Name) {
$cm = Get-CimInstance -Namespace 'root\Microsoft\SqlServer' -ClassName "__NAMESPACE" | ? name -match 'ComputerManagement' | Select-Object -Expand name
$cnp = Get-CimInstance -Namespace "root\Microsoft\SqlServer\$cm" -ClassName ServerNetworkProtocol
$cnp | ? ProtocolDisplayName -eq $Name
}
function Toggle-SqlServerProtocol( $ProtocolObject ) {
Write-Host "Toggle protocol for" $ProtocolObject.ProtocolDisplayName
$params = @{
Name = if ($tcp.Enabled) { "SetDisable" } else { "SetEnable" }
}
$ProtocolObject | Invoke-CimMethod @params
Get-Service MSSQLSERVER | Restart-Service
}
$tcp = Get-SqlServerProtocol 'TCP/IP'
Toggle-SqlServerProtocol $tcp
$tcp = Get-SqlServerProtocol 'TCP/IP'
Write-Host "Current TCP/IP status:" $tcp.Enabled
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

