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 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:
| 1 | .\SetSQLServerStaticPort.ps1 VSQL2019 2019 1455 |
This is how the resulting change looks in SQL Server Configuration Manager:
The dbatools equivalent of the above command would be:
| 1 | Set-DbaTcpPort -SqlInstance VSQL2019 -Port 1455 |
To configure static TCP port 1488 for instance SQL2019_01, running on SQL Server 2019, and also add the two inbound firewall rules:
| 1 | .\SetSQLServerStaticPort.ps1 SQL2019_01 2019 1488 -AddFirewallRules |
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.