Database Administration
sql-server sql-server-2014 security
Updated Sat, 01 Oct 2022 09:33:25 GMT

What ports to open for sql server behind secure domain firewall


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?

Solution

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




Comments (5)

  • +0 – Thanks, very helpful answer. For the connection string, if one is using listener in case of AGs or for mirroring instance where we have c-names do you think related ports need to be open like 5022 for mirroring. Just want to make sure i have all needed info before i requests what all port should be open? — Aug 26, 2022 at 01:46  
  • +1 – If you are connecting to the AG listener, then pretty much everything above applies, except replace "Server" with "Listener". The firewall access should be to the listener's IP address, and on the port that the Listener uses (not the server name). If you connect via both listener and server name, then you'd do both. — Aug 26, 2022 at 01:59  
  • +0 – Thanks, somehow i do not get for one of server where we requested 1433 in addition to 12345 seems to connect. I am confused but why we would need for default port when config for 12345? Also seems for good server browser services disabled whereas BS enabled for not connecting server — Aug 26, 2022 at 15:34  
  • +0 – @Newbie-DBA is the AG listener also running on port 12345? Or is the AG listener using 1433? The instance and AG listener have separate port configurations — Aug 26, 2022 at 21:38  
  • +0 – Thanks for your inputs. We found issues on IP address being wrong. Bummer!! — Aug 26, 2022 at 22:05  

External Links

External links referenced by this document:

References