How to setup SQL Server remote port – Why is it not listening?
I got a request asking if it is possible for a web application to connect to an SQL server remote port. My answer: “But of course! We just need to open port 1433 on the Windows Firewall and enable TCP/IP.” A request handled very quickly. Or so I thought.
But that was not the case. Not long after I said that, I realized it needs a bit more work. The above two steps will not succeed in opening port 1433. Command “netstat -a” confirms this because the server does not listen to port 1433.
So let’s start from the beginning and list what is necessary to open the port for remote access the proper way.
To begin with, it’s a Windows Server 2019 with MS SQL Express 2017 installed. To access the SQL Configuration Manager, the best way is to open Computer Management. There is a snap-in SQL Server Configuration Manager.
Make sure TCP/IP is “enabled”. Double-click it, go to “IP Addresses tab. Now comes the important part.
In the screenshot above you will see several sections. IP1, IP2, IP3 … IPall is the last section. Each section represents the assigned IP addresses to the server. In my case I have an IPv6 public, IPv6 local and IPv4 public address. The field “TCP Dynamic Ports” shows a 0 (zero).
What is a dynamic port?
This is an excerpt from the MS docs: “Once
SQL Service restarts, it requests a free port number from the operating system
and assigns that port to SQL Server.”
What this means is that each time the SQL service restarts, a new port number will be assigned. This is not really what we want.
So what to do?
- To start with decide which IP address you want to use in the respective section. I mean, do you want to open the port for IPv6, IPv4 or both?
- Remove the 0 (zero) in TCP Dynamic Ports
- Make sure Enabled is set to YES
- Scroll down to the last section “IPALL”, replace the 0 (zero) in TCP Dynamic Ports with the port number 1433 in the field “TCP Port”.
- Restart SQL service
Lastly, you need to open port 1433 in Windows Firewall. Run “netstat -a” again. The port should now be listed. You are done!
Finally, you could use another port and not just 1433 – no restrictions there. If the SQL server has a public IP as in my case, I would restrict the source IPs that are allowed to connect.