Blog - comments

Hi Stephane,It depends, you can find the stats for MySQL 5.6 below:sysbench 0.5: multi-threaded sys...
Gregory Steulet

Hi Gregory, how many times does it take to prepare the 20M rows ? It seems take so long time...

Overall, from my point of view there is one key issue: There are Oracle installations with standard ...
Hi Guys, I tried for users tablespace and was able to do the below to recover the datafile in pdbs.P...
Harsha C R
Hi Mark, I fully understand your point... With Standard Edition and Standard Edition ONE, if you are...
Gregory Steulet
Blog Stephane Haby SQL Server 2012: Configuring your TCP Port via Powershell

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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.



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:




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

Rate this blog entry:

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.



  • Guest
    Ashish Dhyani Wednesday, 20 May 2015

    Hello sir Alter command is not working in my powershell.


    Alter: Exception calling "Alter" with "0" arguments

  • Guest
    STH Monday, 15 June 2015


    Since SQL server 2008, the Alter command is without arguments.
    Microsoft link:

    The common mistake is to forget the computer and the instance name in this variable:
    $uri = "ManagedComputer[@Name='Server_Name']/ ServerInstance[@Name='SQL_Name']/ServerProtocol[@Name='Tcp']"

    I hope this can help you or send me directly your code.

Leave your comment

Guest Wednesday, 29 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter