2

We have a single web server with SQL server etc (all MS, server 2008 web etc).

We need to allow access to the database directly so our customer can write reports (it's the way SSRS is!). We original had the DB just sitting on the internet, you wouldn't believe how many attempts were made to get into the sa account!

sa account renamed to something else now, and we are using the Windows Firewall.

thing is, some of the customers work from home, with dynamic IP addresses, so they have to mail each day/weeek/router reboot, and I have to remote desktop in to allow that IP address.

IS THERE A BETTER WAY? I was thinking of a web interface for the firewall, they can add their own IP address, or preferably, they just log in, and it allows access to everything from that IP, or something like that.

4 Answers 4

3
+50

If this is a company setup we're talking about here, there's no reason at all for that SQL server to be accessible from the internet.

If you need to allow access from roaming clients, use a proper firewall with a VPN solution. Watchguard and Palo Alto both have really good VPN solutions. Also check if a current firewall of the company has this feature already, most do.

Again, I can not stress this enough, use a proper firewall!

2
  • Could you elaborate on the differences of a "proper firewall" compared to Windows Firewall? What features could be useful in the scenario? Feb 13, 2020 at 11:31
  • The Windows firewall is meant to protect a single host from exposure, but for an application like this, you'd want a standalone firewall, either a physical device, or a virtual appliance, that's intended to be exposed to the Internet directly. Defence in depth is also a factor, you don't want a single defence failing to leave you without any defence, for example, if the Windows Firewall is turned off, you aren't suddenly exposing every port to the Internet.
    – Stuggi
    Feb 14, 2020 at 5:36
2

We have a single web server with SQL server etc

Possible a poor design choice - web servers and databases both like to have lots of "room" to run around in. Having both on the same server might give you contention problems.

We need to allow access to the database directly so our customer can write reports ... SSRS

Why? If you already have a "web server, SQL Server, etc." on this machine, why not install SSRS as well and give them access [to the web interface] to that?

We original had the DB just sitting on the internet ...

No. Just, no.

Web servers have built-in protections against the kind of "nastiness" that exists "Out There". Databases servers do not. You must "insulate" your databases from the "undesirables".
Running the SSRS application through a Web Server is a straightforward way to achieve this.

1

I think a VPN would be a good solution (if I've understood correctly your needs). You can easily set a free and very secure one using OpenVPN (you get the chance to use username/passwords and 2048 bit certificates: pretty sure, if you ask me). The fastest and easiest way I've found to implement this solution is using a rather old pc (any p4 with 2 network card will do the job) and Zeroshell (http://www.zeroshell.net/). Feel free to ask any kind of details :)

1

You definitely do not want to let external parties configure the firewall on a production database server from the Internet. For your own convenience, though, it is possible to script Windows firewall configuration using PowerShell, for example to feed rules from a text file or similar. However this doesn't solve your immediate problem.

Is re-thinking how clients connect to the service a possibility? Giving clients access to an otherwise secured Windows machine in your network (with the necessary client software installed) via something like Apache Guacamole, or a VPN (as per @Pittos suggestion) would let you shift recurring security changes away from the database server. You would also be managing user credentials rather than firewall rules. This probably lowers the risk of mistakes significantly.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .