How to setup SQL Server Remote Connection

How to setup SQL Server remote port 1433 – 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.

Update: MS SQL Express 2019

If you are using SQL Express 2019, then it’s a lot easier. The steps in the “what to do” section are no longer required. Just be sure to set to “Yes” in the respective IP section for the “Enabled” field. The Windows Firewall bit is still required and make sure to test it with the “netstat -a” command.

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.”
This means that a new port number will be assigned each time the SQL service restarts. This is not really what we want.

So what to do?

1. 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?
2. Remove the 0 (zero) in TCP Dynamic Ports
3. Make sure Enabled is set to YES
4. 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”.
5. 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 port 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.