CODEWITHSUNDEEP
sql-servervb.netsql-server-2008-r2command-promptvb.net-2010
user3579618
user3579618

Reputation: 87

How to change SQL Server Instance TCP Ports to 1433 using CMD?

I'm thinking if it possible to change SQL Server 2008 Instance TCP Ports to 1433 using cmd and without using SQL Server Configuration Manager.

I just want to apply this to my application setup, My client wants it to be in multi-user mode.

I know how to enable TCP/IP using cmd. And I know how to create a script to allow a specific port in windows firewall.

Thanks in advance. Please leave a comment if my details are not enough to answer my question.

Upvotes: 4

Views: 7523

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

Six years (almost exactly) have elapsed since I wrote the answer above. And the state of the art has progressed since then. While I'm sure the previous answer still works, today I'd use Set-DbaTcpPort from the dbatools powershell module. Check it out!

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32697

Not quite cmd, but powershell is the new cmd. :)

import-module sqlps;
$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') "computername"
$instance = $MachineObject.getSmoObject(
   "ManagedComputer[@Name='computername']/" + 
   "ServerInstance[@Name='instancename']"
)

$instance.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = "1433"
$instance.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ""
$instance.ServerProtocols['Tcp'].Alter()

Upvotes: 11

user3777201
user3777201

Reputation:

If you want to change the port through your setup program, you'll have to edit the registry directly for the instance of SQL Server you're targeting. I would highly advise not to do this as you may end up choosing the incorrect instance, or worse yet make an incorrect change and corrupt the values and registry keys.

However, if you still feel that this is something you want to do automatically the registry keys are under the following:

SOFTWARE\Microsoft\Microsoft SQL Server\InstanceID\InstanceName\SuperSocketNetLib\TCP

Where the instance name will have to be figured out (which can also be done through the registry, depending on version will change where it is). You'll also need to figure out which IP (unless you want to change the IPALL value) is the one you're targeting as ALL IPs are listed in their own sub-folder for the instance.

Upvotes: 1

Related Questions