CODEWITHSUNDEEP
sqlsql-serverdelphiregistry
M.Vakili
M.Vakili

Reputation: 90

Change SQL Server default TCP port

I want to change the SQL Server default TCP port in server. I know the default port is 1433, and I know how do this with UI, but I need a code such as registry or batch file or programming language like Delphi, VB, C#

Thank you

Upvotes: 1

Views: 4212

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

Here's what I have in a Powershell script that's part of my server build from a couple of jobs ago:

param (
    [string]$server_name = $(Read-Host -prompt Server)
)
$Machine = new-object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $server_name

$instance = $Machine.ServerInstances[ 'MSSQLSERVER' ];

$instance.ServerProtocols[ 'Tcp' ].IsEnabled = $true;
$instance.ServerProtocols[ 'Tcp' ].Alter();

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

It assumes a non-named instance of SQL Server and that the port you want to set is 14330. Adjust to meet your requirements.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

Below is an excerpt from the dockerfile used to build the official Microsoft SQL Server 2017 image for Windows.

RUN stop-service MSSQLSERVER ; \
    set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value '' ; \
    set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433 ; \
    set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2 ;

The Set-ItemProperty PowerShell commands configure the SQL Server ports and set the login mode to allow both SQL Server and Windows authentication. These same commands can be run against a local SQL instance after changing the registry paths as appropriate for your version and installation as well as the desired port number. The instance needs to be restarted for the changes to become effective.

Upvotes: 0

Related Questions