3

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?

1
  • Note that your "very secured" server becomes somewhat "less secured" when you open a port on your firewall. You should only allow traffic from the expected client's IP address(es) to the server if you decide to open a firewall port. Aug 26, 2022 at 13:32

2 Answers 2

7

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
5
  • 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?
    – Newbie-DBA
    Aug 26, 2022 at 1: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.
    – AMtwo
    Aug 26, 2022 at 1:59
  • 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
    – Newbie-DBA
    Aug 26, 2022 at 15:34
  • @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
    – AMtwo
    Aug 26, 2022 at 21:38
  • Thanks for your inputs. We found issues on IP address being wrong. Bummer!!
    – Newbie-DBA
    Aug 26, 2022 at 22:05
-1

While I second with other answer, the application got nothing to do with port# 5022, it is used for Database-replication from AG's Primary Replica to Secondary. For the Application, only the client port is required, the default is 1433 or any custom port.

In addition, you can verify and/or ensure to maintain SQL Server securely using the benchmarks..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.