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.
Weve 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?
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.
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.am2.co,12345. Similarly, in an application connection string you'd use the same format for the
Server parameter, such as
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
External links referenced by this document: