Infrastructure at your Service

Stéphane Haby

SQL Server 2012: Configuring your TCP Port via Powershell

Changing the default TCP port 1433 for SQL Server is an important step for securing your SQL Server. I have written a script in PowerShell to modify the port number that helps me perform this task. This blog posting will show you how to do it. Please note that the code in this article is for SQL Server 2012 on Windows Server 2012.

Initialization

Run SQLPS to launch the SQL PowerShell in a command prompt.

First you have to initialize the Microsoft.SqlServer.Management.Smo namespace that contains the classes representing the core SQL Server database engine objects:

$smo = 'Microsoft.SqlServer.Management.Smo.'

Then, you have to set the ManagedComputer object that represents a Windows Management Instrumentation (WMI) installation on an instance of Microsoft SQL Server.

$wmi = new-object ($smo + 'Wmi.ManagedComputer')

Let me show you my instance information with $wmi (sample):

You will notice that in ClientProtocols, I have the tcp protocol.

Searching the port value

The next step is to find the Tcp Protocol settings:

 

$uri = "ManagedComputer[@Name='']/ ServerInstance[@Name='']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)

This is my $tcp variable (sample):

Please not that IsEnabled is set to true. If you have IsEnabled on false, you can set it up to true with this command:

$Tcp.IsEnabled = $true

And do not forget to validate this change with an Alter:

$Tcp.Alter()

To check the port, you need to go to the IPAll characteristics:

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

Like in the SQL Server configuration manager, two sections will appear: One is for the TcpDymanicPorts and the second for the fixed TcpPort (sample):

 

Changing the TCP port

To change the value, it’s very simple, the field is simply named Value:

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

If you look at the characteristics with your precedent command…

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

…the port has changed, but in the Sql Server Configuration Manager, the port still is 1433:

But why??? Cry

Validating the change

Yes, of course… you need to validate the change with the Alter command!

And now, if you look at the SQL Server Configuration Manager, the change is applied:

To finish, do not forget to restart your services to activate the port change.:-D

 

Leave a Reply


+ six = 15

Stéphane Haby
Stéphane Haby

Delivery Manager