dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, 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 the postings!
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='
$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.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:
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.