I’ve previously mentioned how I’m building out a whole bunch of clustered servers. In an effort to standardize all the installation and configuration components I’ve used multiple PowerShell scripts. One of the most challenging to get working was one that enabled the TCP/IP for SQL and set it to listen on Port 1433.
This script is based on the great post by Allen White (blog|twitter) on finding your enabled SQL Server protocols using PowerShell.
Is TCP/IP enabled?
For the script to work we have to load the SMO objects with the context of the SQL Server we want to change (if a clustered instance we would use the virtual name)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName
Then we build the path to the TCP/IP protocol in the instance and query the IsEnabled property.
$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"
$Tcp = $m.GetSmoObject($urn)
$Enabled = $Tcp.IsEnabled
Now to enable TCP/IP if it’s disabled.
IF (!$Enabled)
{$Tcp.IsEnabled = $true }
Set the listening port
Now we drill down a little further and set the listening port.
$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"
$TCP.alter()
Now for the fun stuff
What we have there is strictly functional. There’s no error checking or funky components.
First off, we need to run this as admin, so we can check to see if this is an administrator running this in an elevated shell:
$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
We can then check the value of $RunningAsAdmin. If true then continue on our way, otherwise error out and warn the user.
Next we’ll prompt the user for the SQL instance that we’ll be configuring, after all that’s pretty important. The entered value will be placed in the $SQLInstance variable and used in the script.
$SQLInstance = read-host "Enter the SQL instance to configure"
We’ll need to split out the server or virtual name from the instance name (if there is one) in order to use the values within the SMO portion (we’ll also make everything upper case).
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$SQLName = $string[0]
$Instance = $string[1]
}
else
{
$SQLName = $SQLInstance
$Instance = "MSSQLSERVER"
}
Given that we are making a significant change we should provide the option to cancel the action. We do this with a quick confirmation.
$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""
$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""
$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)
$captionTCP = "Question!"
$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"
$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)
We can then check whether yes or no button was pressed and handle the action.
if($resultTCP -eq 0) { #SET TCP STUFF HERE }
else { Write-Warning "TCP/IP changes cancelled" }
Quick and easy
The script only takes a few seconds to run and does exactly what is says on the box. You can copy the script below or download here. Happy playing.
#Check the executing user is running in an elevated shell & and an admin
$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
if ($RunningAsAdmin)
{
<######################################
## WORK HERE ##
######################################>
#Get the SQL instance we'll be working with
$SQLInstance = read-host "Enter the SQL instance to configure"
$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""
$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""
$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)
#########################################
### ENABLE TCP/IP & LISTENING ON 1433 ###
#########################################
$captionTCP = "Question!"
$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"
$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)
#If yes prompt for confirmation then make the changes
if($resultTCP -eq 0)
{
Try
{
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$SQLName = $string[0]
$Instance = $string[1]
}
else
{
$SQLName = $SQLInstance
$Instance = "MSSQLSERVER"
}
$SQLName
$Instance
# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
Trap {
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
write-output $err.Message
};
continue
}
# Connect to the instance using SMO
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName
$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"
$Tcp = $m.GetSmoObject($urn)
$Enabled = $Tcp.IsEnabled
#Enable TCP/IP if not enabled
IF (!$Enabled)
{$Tcp.IsEnabled = $true }
#Set to listen on 1433
$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"
$TCP.alter()
"Success: SQL set to listen on TCP/IP port 1433. Please restart the SQL service for changes to take effect."
}
Catch { Write-Warning "Unable to enable TCP/IP & set SQL to listen on port 1433" }
}
else { Write-Warning "TCP/IP changes cancelled" }
}
else { Write-Warning "This script must be executed by an administrator in an elevated shell" }
SirSQL.net 
The script only takes a few seconds to run and does exactly what is says on the box. You can copy the script below or download here. Happy playing.-Mallivi Leather designer shoes
LikeLike
Thanks. It works well only minor suggestion if it fails with a RPC Server unavailable issue it still reports a success.
./ChangePorts.ps1
Enter the SQL instance to configure: xxxx
xxxxx
Exception has been thrown by the target of an invocation.
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
Exception has been thrown by the target of an invocation.
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
Success: SQL set to listen on TCP/IP port xxxx. Please restart the SQL service for changes to take effect.
LikeLike
Exception has been thrown by the target of an invocation.
SQL Server WMI provider is not available on instancename
Invalid namespace
Exception has been thrown by the target of an invocation.
SQL Server WMI provider is not available on instancename
Invalid namespace
Success: SQL set to listen on TCP/IP port 1433. Please restart the SQL service for changes to take effect.
LikeLike