Windows Firewall Rules For SQL Management Studio

The purpose of this post is to find out why I cannot log into a remote SQL Server instance from a Windows 10 system. My Windows 10 system connects just fine, and has fire wall rules that are too many and to un-restrictive.

Therefore, I would like to tighten my Windows 10/Windows Defender inbound and outbound firewall rules that I believe allow SQL Server Management Studio to talk to a SQL Server on a remote node. Both the client and server are on the same domain.

The SQL instance on the remote server uses dynamic port 49365.

I have as an inbound rule, unrestricted TCP for local and remote ports. To which ports should this rule be narrowed?

For my outbound rule, I have a UDP protocol for all local ports and remote port 1434.

I believe this crazy configuration allows SQL Management Studio to talk to a remote SQL server on dynamic port 49365.

Questions 1: What should my firewall rule settings really be?

(I'm going to ask Question 2 as a separate OP.)

Solution

First the easy part: the client doesn't needs any inbound connection, as it doesn't receive any connection (it makes them), so you can safely block everything inboud.

Now for the outgoing ones. The server itself only needs TCP access in the port it's listening to, so if you have a fixed port, you just open it (by default 1433 for a default instance) and you're good to go.

But since you're using dynamic ports, setup is a bit harder. Basically, "dynamic port" means that the server listen on a "random" port each time it starts, and the SQL Browser service tells clients on which port is listening each instance (this is the default setup for named instances).

So for this, first you need to allow outgoing connections to the SQL Browser, which listens on UDP 1434. Now you'll also need the normal server connection as before, which is still at TCP, but this time the port is unknown (since it's random). So, at most the most restrictive rule you can make is to allow all TCP ports, maybe also filtered by client program (ssms.exe for example) or by any other parameter that your firewall supports.