Changing the SQL Server Port and Firewall Rules
A default instance of SQL Server listens on TCP 1433, and every port scanner and worm on the internet knows it. Moving the port is not real security, but it does remove your server from the automated noise, and understanding how the port, the SQL Browser service and the Windows Firewall interact saves a lot of "cannot connect" tickets. Named instances are a different problem: they get a dynamic port that can change after a restart, which breaks anything with a hard-coded firewall rule.
I got tired of chasing "cannot connect" tickets after restarts, so now I give every instance a fixed port. Here's how I do it on Windows and on Linux (SQL Server 2017), open exactly that port in the firewall, and point clients at it. You need local admin plus sysadmin, and a maintenance window because the service has to restart. Find out first whether clients connect by server or server\instance.
What's in use now
SELECT local_tcp_port, local_net_address, protocol_desc
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name IN ('TcpPort', 'TcpDynamicPorts');
If TcpDynamicPorts has a value and TcpPort is empty, the instance is using a dynamic port and SQL Browser (UDP 1434) is resolving the instance name for clients.
Static port on Windows
Open SQL Server Configuration Manager, expand SQL Server Network Configuration, Protocols for MSSQLSERVER (or your instance name), then TCP/IP, Properties. On the IP Addresses tab, scroll to the bottom to IPAll. Clear TCP Dynamic Ports (make it empty, not 0) and set TCP Port to the new value, for example 14330. Click OK and restart the SQL Server service.
The same change through PowerShell, using the SQL Server WMI provider (adjust the instance name and SQL Server version namespace as needed):
Import-Module SqlServer
$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$ipAll = $tcp.IPAddresses['IPAll']
$ipAll.IPAddressProperties['TcpDynamicPorts'].Value = ''
$ipAll.IPAddressProperties['TcpPort'].Value = '14330'
$tcp.Alter()
Restart-Service MSSQLSERVER -Force
Static port on Linux
On SQL Server 2017 for Linux the port is a setting in mssql.conf:
sudo /opt/mssql/bin/mssql-conf set network.tcpport 14330
sudo systemctl restart mssql-server
Check with sudo ss -lntp | grep sqlservr.
Firewall
On Windows Server, add an inbound rule for the specific port. I avoid the "allow sqlservr.exe on any port" approach unless dynamic ports are unavoidable, because it's hard to audit later:
New-NetFirewallRule -DisplayName "SQL Server 14330" -Direction Inbound -Protocol TCP -LocalPort 14330 -Action Allow -Profile Domain,Private
Older servers or scripts can use netsh:
netsh advfirewall firewall add rule name="SQL Server 14330" dir=in action=allow protocol=TCP localport=14330
If you are moving off 1433, remove the old rule too. On Ubuntu with ufw:
sudo ufw allow from 10.0.0.0/24 to any port 14330 proto tcp
sudo ufw delete allow 1433/tcp
On CentOS 7 with firewalld:
sudo firewall-cmd --permanent --add-port=14330/tcp
sudo firewall-cmd --permanent --remove-service=ms-sql
sudo firewall-cmd --reload
Clients
Clients specify the port with a comma: sqlsrv01,14330 in SSMS, in sqlcmd -S sqlsrv01,14330, and in ADO.NET connection strings as Server=sqlsrv01,14330;. JDBC uses a colon instead: jdbc:sqlserver://sqlsrv01:14330.
If you would rather not touch every connection string, create a client alias in SQL Server Configuration Manager under SQL Native Client Configuration, Aliases (both 32-bit and 64-bit nodes), mapping the old name to the new port. Alternatively, once you use a fixed port for a named instance you can stop and disable the SQL Browser service entirely, provided every client includes the port.
Did it work?
From a client machine, check the port is reachable before blaming SQL Server:
Test-NetConnection -ComputerName sqlsrv01 -Port 14330
Then connect and confirm the listening port from the server side:
SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;
Also check the SQL Server error log after the restart; it contains a line like "Server is listening on [ 'any'
When it goes wrong
"A network-related or instance-specific error occurred ... (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)" Either the port in the connection string is wrong or the service did not pick up the new setting. Read the error log for the "listening on" line.
"provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified" The client used server\instance without a port, and SQL Browser is disabled or UDP 1434 is blocked. Add the port to the connection string or re-enable Browser.
The port changed back after a restart. TCP Dynamic Ports was set to 0 rather than being blank. A 0 means "assign dynamically at startup". Clear the field completely.
Rule exists but nothing gets through. The rule was created only for the Public profile while the server's network is classified as Domain. Check Get-NetConnectionProfile and set the rule's -Profile accordingly.
If you do only one thing, put the port in every connection string so nothing depends on SQL Browser.