This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
What ports to open for sql server behind secure domain firewall
Question
We are trying to solve connectivity issues for one of the app trying to pull data from very secured sql server behind firewall. I am not that expertise in security but need help on how to proceed in opening ports for that app to pull data from sql server behind secure domain.
We’ve opened the port which sql server is listening on lets say 12345 but still no luck.
How do i know what additional ports may need to be opened like udp 1434 default 1433 or mirror like 5022? Is there way we can find this info?
asked 2022-08-25 by Newbie-DBA
Answer
If you are using a standalone SQL Server instance, the answer is pretty straightforward. If you have an Availability Group, and are using the AG Listener to connect, then there you would need to do additional firewall rules for the AG listener.
Standalone SQL Servers
If SQL Server is running on a port other than 1433, you need to tell your app which port to connect on.
One way of doing this is by letting SQL Server Browser Service broadcast that information. Given that you described this as a "very secured" environment, this option might not be the best. However, if you do wish to take this approach, you’d need to allow UDP 1434.
A second option is simply to include the port number in your connection string. In SSMS, you’d simply enter the server name in the format ServerName,port, such as MySecureServer,12345 or MySecureServer.am2.co,12345. Similarly, in an application connection string you’d use the same format for the Server parameter, such as Server=MySecureServer,12345;Database=myDataBase;Trusted_Connection=Yes.
Availability Groups
If you’re connecting to an Availability Group Listener instead of (or in addition to) the server name, then you would need to have the firewall allow access to the Listener IP & port instead of (or in addition to) the server name, and you would use the Listener name & port in your connection string.
As a DBA, I usually need to connect to both the instance itself, and the AG Listener, so I would configure firewall rules for both the Instance IP & its port, and the AG IP(s) and associated port(s). However, depending on your environment and security restrictions you may not need/want to do both.
You can determine the full list of IPs and ports used by the AG listener(s) by using the following query:
SELECT l.dns_name, l.port, i.ip_address
FROM sys.availability_group_listeners AS l
JOIN sys.availability_group_listener_ip_addresses AS i
ON i.listener_id = l.listener_id
answered 2022-08-25 by Andy Mallon