Tag: Network

Set The SQL TCP/IP Port With PowerShell

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" }