Managing Server Network Protocols with WMI Provider

To view the list of properties associated with each network protocol for the default instance on the local computer:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty -filter "InstanceName = 'MSSQLSERVER'" | Select-Object ProtocolName, PropertyName, IPAddressName

 

To change the port number for IPAll from 1433 to another port — for example, 3660:

# NAME: ChangeDefaultPortNumber.ps1
# COMMENT: This script change the port number of the default instance from 1433 to 3660
$wmi=Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty -filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'"
$wmi.SetStringValue(3660) | Out-Null
$sqlservice = Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName='MSSQLSERVER'"  #ServiceName='MSSQL`$INSTANCE1'
$sqlservice.StopService() | Out-Null
$sqlservice.StartService() | Out-Null
# Confirm the default port number has been changed
Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty -filter "PropertyName=’TcpPort’ and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'" | Select-Object PropertyStrVal

To assign different port numbers to different IP addresses,the complete script,DisableListenOnAllIPs.ps1, is shown here:

# . for the local computer
# If you want to connect to a remote machine, specify the machine name here.
$strComputer = "."
# Name of the targeted SQL Server instance. Here the default instance is targeted.
# For a named instance INSTANCE1, use "INSTANCE1".
$strInstanceName = "MSSQLSERVER"
$wmi=Get-WmiObject -computerName $strComputer -namespace
root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty -filter "PropertyName=’ListenOnAllIPs’
and InstanceName=’$strInstanceName’"
$wmi.SetFlag(0) | Out-Null
$sqlservice = Get-WmiObject -computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class SqlService –filter "ServiceName=’MSSQL`$$strInstanceName’"
$sqlservice.StopService() | Out-Null
$sqlservice.StartService() | Out-Null
$wmi=Get-WmiObject -computerName $strComputer -namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class ServerNetworkProtocolProperty -filter "PropertyName=’ListenOnAllIPs’
and InstanceName=’$strInstanceName’"
# Confirm the ListenOnAllIPs property has been disabled.
Write-Host "The value of the ListenOnAllIPs property is set to " $wmi.PropertyNumVal