Use PowerShell to set TCP Port for SQL to 1433

Question

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...

Agh, my eyes! That uri! Use string expansion. `$uri = "ManagedComputer[@Name='$Env:ComputerName']..."`. Also, run a `$NPPropTCP[1] | Get-Member` before trying to access its members. — Maximilian Burszley, Jan 23 '18 at 21:09
Sorry about how ugly this is... I haven't used this enough to understand the whole concept of making it look pretty... So when I run the $NPPropTCP[1] | Get-Member I get the error: get-member : You must specify an object for the Get-Member cmdlet. And I apologize... I don't use Powershell regularly enough to know most of the basics, and almost all I do is by piecing together stuff I find on the web. — Christopher Cass, Jan 23 '18 at 22:25
Okay. just putting "$NPPropTCP | Get-Member" without the 1gives me a name of "Value," a membertype of "Property", and a definition of "System.Object Value {get:set:}" — Christopher Cass, Jan 23 '18 at 22:34
Your problem is you're trying to access an array that doesn't exist. Remove the `[1]` accessor — Maximilian Burszley, Jan 23 '18 at 22:36
Sadly, my problem is I have no idea what I'm doing... And I'm further confused, as so far as I can tell, you're the one that introduced the [1] into this when you edited my code (which I appreciate, but it doesn't appear to work either way) — Christopher Cass, Jan 23 '18 at 22:40

postanote · Accepted Answer · 2018-01-24T01:04:06.740

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 : {}

Thank you for the help. Unfortunately, when I get to "($Tcp = $wmi.GetSmoObject($uri))" It says: "Exception calling "GetSmoObject" with "1" argument(s): "Attempt to retrieve data for object failed for ManagedComputer" — Christopher Cass, Jan 25 '18 at 14:59
No worries, it happens. So if all things are working for you via my answer be sure to mark it as you accepted answer. take care — postanote, Jan 25 '18 at 17:53
Doing it now. Just finished the script and verified everything is working! — Christopher Cass, Jan 25 '18 at 18:11

Use PowerShell to set TCP Port for SQL to 1433

1 Answers1

Linked