Experts,

A client of mine is placing SQL servers behind DMZ. This comprises of named(having dynamic ports)/default instances having analysis and reporting services. The application would be connecting to the SQL server from outside the DMZ zone. Not allowed to make any changes to the existing setup. Only can have ports opened. Can you please suggest which all ports would need to be opened n this case.

Hello,

See:
Configure a Windows Firewall for Database Engine Access
Configure the Windows Firewall to Allow Analysis Services Access
Configure a Firewall for Report Server Access

Thanks Olaf. But what I understand is that the firewall is not a windows one , but through network design. Hope I am making sense

The ports would be the same, no matter the firewall.

Thanks Erland for the clarification. In case of a windows firewall we can put the sqlserv.exe file in exemption to take care of the named instance ports. How do we go about it for a hardware based firewall where the sql ports are bound to change being dynamic.

where the sql ports are bound to change being dynamic.

Dynamic assigned IP port won't work in scenario, they must be assigned static to a fix port.

For a reference: Configure a Server to Listen on a Specific TCP Port (SQL
Server Configuration Manager https://msdn.microsoft.com/en-us/library/ms177440.aspx

Note that a dynamic port isn't that dynamic in the first place. It will only change if the SQL Server is stopped, and some other process "steals" that port number. And if you instead selected "static" port, that other process can still "steal" the port, so the difference is only that when you use "static", then you SQL Server refuses to start.

I haven never seen a case where the ("dynamic") port number was "stolen" by some other process. Theoretically possible, but unlikely.

IMO, MS chose poorly how they phrases things in the GUI where you configure port numbers.

Thanks a lot for all your help. One more query if I schedule a backup from a sql server behind a DMZ to a disk on internal network, I am assuming would need to open the static ports of the sql server on the firewall. Any other ports to opened in this case.

SQL server uses the default port 1433. Make sure your fire wall is allowing all connections for inbound and outbound.

If your sql is running on different port then it should be used.

you can check this from configuration manager>>network connections>> TCP/IP>> properties

I don't know what you mean by "open the static ports of the sql server on the firewall", but if your SQL Server is to product a backup files on a machine on the internal network, then you need to open ports in your firewalls to allow your SQL Server to create a file on the machine on the internal network. Nothing strange here. Just as if you would logon as the SQL Server service account and copy a file onto a share on a machine in the internal network.

Thanks Tibor. To clarify if the SQL Server behind a DMZ using port number 1437 is doing a native backup to a disk on internal network , the port 1437 would not be needed to be opened in the firewall. Just the ports to copy a file from one server to another.

What would be the status if the same file is used for restore from the Internal disk to the SQL server behind DMZ. I presume then the port 1437 would need to be opened

• Edited by VG2104 11 hours 20 minutes ago

There would be no difference between BACKUP or RESTORE.

If port 1437 is the port SQL Server is listening on, this has nothing to do with RESTORE.