Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use PowerShell to set TCP Port for SQL to 1433

I've currently got the TCP Port in SQL Server Configuration Manager showing as blank. I need to figure out how to use PowerShell to change this to port 1433. I've been able to run this to actually get the value:

$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer')

$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + 
"']/ServerInstance[@Name='SQLSERVER']/ServerProtocol[@Name='Tcp']"
$Np = $wmi.GetSmoObject($uri)
$NPProp = $Np.IPAddresses.where({$_.Name -eq 'IPALL'})
$NPPropTCP = $NPProp.IPAddressProperties.where({$_.Name -eq 'TcpPort'})
$NPPropTCP.Value

However, when I run:

$NPPropTCP[1].Value = '1433'

I get the following error:

The property 'Value' cannot be found on this object. Verify that the property exists and can be set.
At line:20 char:1
+ $NPPropTCP[1].Value = '1433'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [],
ParentContainsErrorRecordException
+ FullyQualifiedErrorId : PropertyNotFound

I figure this is probably something simple, but I'm stuck...

like image 808
Christopher Cass Avatar asked Aug 31 '25 20:08

Christopher Cass


1 Answers

Updated to show results of each command

You have mistakes / missing things in your code.

Try this approach...

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


Results

Loading SQLPS environment


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


Results

Initializing WMI object and Connect to the instance using SMO


ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQLFDLauncher, MSSQLSERVER, SQLBrowser, SQLSERVERAGENT}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {MSSQLSERVER}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='SQL01']
Name               : SQL01
Properties         : {}
UserData           : 
State              : Existing



($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")


Results

ManagedComputer[@Name='SQL01']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']



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


Results

Parent              : Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance
DisplayName         : TCP/IP
HasMultiIPAddresses : True
IsEnabled           : True
IPAddresses         : {IP1, IP2, IP3, IP4...}
ProtocolProperties  : {Enabled, KeepAlive, ListenOnAllIPs}
Urn                 : ManagedComputer[@Name='SQL01']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']
Name                : Tcp
Properties          : {Name=DisplayName/Type=System.String/Writable=False/Value=TCP/IP, Name=HasMultiIPAddresses/Type=System.Boolean/Writable=False/Value=True, 
                      Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True}
UserData            : 
State               : Creating


$Tcp.IsEnabled = $true


($Wmi.ClientProtocols)


Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : Named Pipes
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 3
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {Default Pipe}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='np']
Name               : np
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=Named Pipes, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=3}
UserData           : 
State              : Existing

Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : Shared Memory
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 1
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='sm']
Name               : sm
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=Shared Memory, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=1}
UserData           : 
State              : Existing

Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : TCP/IP
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 2
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {Default Port, KEEPALIVE (in milliseconds), KEEPALIVEINTERVAL (in milliseconds)}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='tcp']
Name               : tcp
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=TCP/IP, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=2}
UserData           : 
State              : Existing



$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 14433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}


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

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 1433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}


'Save properties'
$Tcp.Alter()

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 1433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}
like image 99
postanote Avatar answered Sep 03 '25 10:09

postanote