May 14, 2020 at 9:34 pm
I am setting up a Win2019/Sql2019 server. Setting up the firewalls for the default services are fairly straight forward using the article:
All the information is for inbound connections. I'm trying to connect to a named instance through the firewall which is an outbound connection. I can add a specific firewall rule for each named instance on every SQL server that needs it, but I was looking for more a general rule that I can add to each server without having to hardcode the port on the remote server and having to hardcode the firewall rule to that port.
May 15, 2020 at 3:18 pm
The browser running on UDP 1434 (https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver15) is what the source instance would connect to on the target instance and get the port.
Outbound traffic usually isn't too much of an issue on the instance, but the inbound would need the UDP port open. That might have changed in more modern WS2019, and I'd have to verify you could get out on UDP 1434. However, the listener port on the named instance is a range, and while you could open a range, you'd be better off setting a specific port for the listening. This way you can limit firewall rules and only open what you need. The browser still can tell a client which port is being used, without you having to code this into clients.
This links to the range of ports:
May 15, 2020 at 3:29 pm
That is how a firewall is designed though. There are no "shortcuts" with security.
The ONLY other way you can set that up is to allow applications through the firewall instead ports, but I personally find that to be more trouble that it is worth. Ports are much easier to configure and maintain long term.
Plus, if you set it up through a GPO, then you don't need to set it up more than once and it'll work with your failover servers or when you do an OS migration.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 16, 2020 at 12:58 am
I found inbound traffic was not an issue (so far...). Its my outbound. In my example, the outbound server (ServerA ) has firewall restrictions and so I need to make sure the correct ports are set. The server (ServerB) that it's going to has no firewall restrictions. I'm unable to get a connection from ServerA to named instance on ServerB. SQL Browser service is turned on, on ServerB and I'm able to connect to it successfully not as restricted systems. So I fairly certain there is a firewall rule that I'm missing on ServerA.
@Brain Gale
I'm not trying to necessary shortcut security, but are we saying that there is no way to manage SQL Server to SQL Server connections using dynamic ports with firewall rules? So for my current scenario I'm assigning an outbound rule for a small range of ports (i.e. 40001-40005) and any instances assigned to any of those ports, it'll be fine.
May 16, 2020 at 2:39 pm
If you assume you only have 5 instances max on a machine, then I think assigning ports to those instances and allowning that range through makes senes. I though 50000+ was the user assigned, so I might open firewalls as 51433, 51434, 51435, etc. Since that is easier for me to remember over time. 1433 for default, 51433+ for named.
May 17, 2020 at 8:04 am
@steve-2 jones, this is exatly what I think for my testing and plan to propose to our SQL team. I don't think we'll have more than 2-3 named instances on a server, but just case, I set it to 5. I looked at the IANA port listings to look for some free range of ports and are using those. From what I've read, 49152 to 65535 are the dynamic port ranges. So I stuck with the 40k range of numbers.
After readying through this and having some time to ponder it and reviewing @Brian Gale comments, I've come to the conclusion that with restricted firewalls, dynamic ports for SQL are no longer usable at least from the perspective of the SQL to SQL server connection. It just took me a while to sink into my brain.
May 17, 2020 at 3:53 pm
I think you're on the right track. Dynamic ports aren't good for security, so I'd set specific ones, leave the browser off, and then use known ports, or use port forwarding.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply