Changing SQL server TCP port with powershell

Question

_{Thursday, July 30, 2015 4:13 PM}

Hi All.

Does anyone know how to change the SQL server network TCP port number for SQL 2014 using powershell. Previously for SQL 2012 I have used a DLL to do this, but the same DLL doesn't work for SQL 2014.

I would prefer to use powershell directly to achieve this if possible.

Thanks in advance.

All replies (8)

_{Thursday, July 30, 2015 4:22 PM ✅Answered}

Many options here:

https://duckduckgo.com/?q=Changing+SQL+server+TCP+port+with+powershell

_{Friday, July 31, 2015 11:34 AM ✅Answered}

You can also do this:

$instance='SQLExpress'$splat=@{
    Path="HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instance\MSSQLServer\SuperSocketNetLib\Tcp"
    Name='TcpPort'
    Value=51802
}$splat2=$splat.Clone()
$splat2.Remove('Value')
Get-ItemProperty @splat2
set-itemproperty @splat -WhatIf 
Restart-Service "MSSQL`$$instance" -WhatIf

\(ツ)_/

_{Thursday, July 30, 2015 4:37 PM | 1 vote}

(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\your instance name here>\MSSQLServer\SuperSocketNetLib\tcp').TcpPort

Set the port and restart SQLServer instance.

\(ツ)_/

_{Thursday, July 30, 2015 4:41 PM}

One line method:

reg add "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPress\MSSQLServer\SuperSocketNetLib\tcp" /v TcpPort /d 9999 /t REG_SZ

9999 would be the new number

\(ツ)_/

_{Friday, July 31, 2015 10:26 AM}

Hi,

The code snippet above doesn't set the port, do you have an example ?

Thanks.

_{Friday, July 31, 2015 11:19 AM}

That is an example.

\(ツ)_/

_{Friday, July 31, 2015 11:25 AM | 1 vote}

Hi....

Try this replacing with the instance and port.,

Set-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\YOUR_INSTANCE_NAME\MSSQLServer\SuperSocketNetLib\tcp" TcpPort -value YOUR_PORT

Regards,

Venu

_{Tuesday, January 17, 2017 3:07 PM}

For me with SQL 2016 the registry path was a little different. To set static port I used the following command-line where "9999" is an example port number (sorry it's not PowerShell):

reg add "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.<Instance Name>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort /d 9999 /t REG_SZ

Then I cleared the TcpDynamicPorts value:

reg add "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.<Instance Name>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpDynamicPorts /d "" /t REG_SZ

And finally restarted my SQL instance:

net stop MSSQL$<Instance Name>
net start MSSQL$<Instance Name>

With other versions of SQL the "MSSQL13.<Instance Name>" will  have a different version number instead of '13'.