4

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.

user3579618
  • 87
  • 1
  • 10
  • SQL Server doesn't listen on UDP, the browser service - however - does. Multi-user is at the database level not the instance level and has nothing to do with what port SQL Server or the browser service is listening on. Could you update the details a bit :) –  Mar 22 '15 at 02:25
  • Hi @SeanGallardy thanks for you comments, I edited my question, Actually, I already done this thing in manual mode but it needs a techie skills and knowledge to do this. But I want it to be in a single click to do these things. I hope you get me. :) – user3579618 Mar 22 '15 at 02:45
  • This shows where it is stored in the registry. http://stackoverflow.com/questions/2243250/how-can-i-find-sql-server-port-number-from-windows-registry. You can try setting this with a command line tile or with a script – Nick.Mc Mar 22 '15 at 03:46

3 Answers3

10

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()
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
1

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.

0

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!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68