Home » Set SQL Server static port via PowerShell

Set SQL Server static port via PowerShell

by Vlad Drumea
0 comment

This post demos a PowerShell script that I’ve put together to automate the configuration of the static TCP port, and the addition of firewall rules, for a SQL Server instance.

The script is derived from another PowerShell script that I’ve written to help spin up SQL Server test instances in my home lab.

This should be helpful if you’re working in a restrictive environment where you can’t install additional PowerShell modules, and you couldn’t take advantage of dbatools’ Set-DbaTcpPort.

You can find the SetSQLServerStaticPort.ps1 script in my SQL Server Scripts repo.

What it does?

  • Enables the TCP/IP protocol for the instance if it’s disabled.
  • Disables the dynamic TCP port by removing the 0 value from all the IPs.
  • Sets the TCP Port to the provided value for IPAll.
  • If requested, it adds inbound firewall rules for the SQL Server instance and for the SQL Server Browser service.

It works with SQL Server 2016 through 2022. Might work with older versions, but I didn’t test it.

Parameters

  • -InstanceName – the name of the instance for which the change should be made
  • -Version – the SQL Server version of the instance (e.g.: 2019)
  • -StaticPort – the static port number that should be configured
  • -AddFirewallRules – if this switch is used then the script will also add the 2 firewall rules.

Note that the first three parameters are positional, so you don’t need to specify their names, just the values.

The script does the protocol and port changes by editing the registries. And, since SQL Server’s registry keys have the major build number in their names, that’s why the -Version parameter is needed.

How to use it

Open a PowerShell window as admin and run it with your desired values.

To configure static TCP port 1455 for instance VSQL2019 running on SQL Server 2019:

PowerShell window opened as administrator. PS F:\GitHub\SQL-Server-Scripts\PowerShell> .\SetSQLServerStaticPort.ps1 VSQL2019 2019 1455 The TCP/IP protocol for instance VSQL2019 is disabled. Enabling it now. Setting static TCP port 1455 for instance VSQL2019. Restarting services after port changes SEO SQL Server static port PowerShell

This is how the resulting change looks in SQL Server Configuration Manager:

SQL Server static port PowerShell

The dbatools equivalent of the above command would be:

To configure static TCP port 1488 for instance SQL2019_01, running on SQL Server 2019, and also add the two inbound firewall rules:

PowerShell window opened as administrator. PS D:\> .\SetSQLServerStaticPort.ps1 SQL2019_01 2019 1488 -AddFirewallRules The TCP/IP protocol for instance SQL2019_01 is disabled. Enabling it now. Setting static TCP port 1488 for instance SQL2019_01. Adding firewall rules... ->Adding firewall rule 'SQL Server SQL2019_01'. ->Adding firewall rule 'SQL Server Browser service'. Restarting services after port changes SQL Server static port PowerShell

And this is how the two firewall rules look in Windows Defender Firewall with Advanced Security (WFAS):

Conclusion

Making basic port changes in SQL Server should be a hassle even if you can’t use dbatools.

While on the topic of network-related configurations, you might also be interested in this post about SQL Server client aliases.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.