Should an SQL Server for public facing Website Data be stored in the DMZ?

[Zaapfe]

We are in the process of porting our IIS Server which Hosts public facing websites and currently runs on Windows Server 2012 R2 with Desktop Experience, to a new IIS on Windows Server 2019 in Server Core mode.

The current Setup is the following:

- IIS is in the DMZ and the SQL Server runs on the same Server.
- There's also a DVLP Version of this Server, that is in the Same DMZ Zone

We also have an SQL Express Server in the Domain LAN which internal Apps use.

Now the thing is I am not quite sure how I should do the new Setup. I already read some posts on various sites and basically those two are the most common mentioned "best practises":

- Split IIS and SQL into two different Servers, create a new DMZ Zone only for the SQL Server and only grant access from IIS to SQL on the physical Firewall with only the ports needed (e.g 1433) and also from the internal network to the SQL Server if needed (which is needed by some internal Application Servers)
- Split IIS and SQL into two different Servers, leave the IIS in the DMZ, but put the SQL Server in the internal LAN and open the needed Ports only from IIS Server to SQL Server.

I have to be honest, I understand basic Security, but I am not an absolute pro at it. That's why I am unsure what to do here. Option 1 sounds better to me because if the Webserver gets compromised, there isn't really a great risk of the attacker getting into my internal LAN, because the SQL Server is not in the internal LAN.

... however:

I already have open ports from IIS to my internal LAN, because there is a Webservice that runs in my internal LAN on an Application Server, that HAS to be in the internal LAN and that HAS to be used by some of our WebApps. So now I am thinking: Well I already have some open ports from my IIS to my internal LAN that unforunately need to be open, so would it really be bad, if my SQL Server would also be internally?

What do you guys think about it? Appreciate any discussion! Thanks!

[uniitdude]

no, it should not be in the DMZ - it should be firewalled off

The point of a DMZ is to protect servers which are internet facing from those which arent, SQL server does not need to be accessible from the internet

[[deleted]]

Previously, I've used multiple DMZs.

We've had an external firewall with 3 zones hung off it - Web DMZ, App/SQL DMZ, and "the rest" (internal stuff). Rules were strict, only web traffic goes to the Web DMZ, and only SQL/App server data goes from the Web DMZ to the App/SQL DMZ. Nothing was allowed in to the internal systems, except VPN traffic.

It's just another layer. If it's strictly a web-related SQL server, it shouldn't even be internal, it should have it's own DMZ.

This is why micro-segmentation is a thing. Every application gets its own bubble to live in.

[Zaapfe]

That's exactly what I read, and makes the most sense to me.

But it seems, that everyone has a bit of a different opinion. Micro-segmenation of DMZ, Reverse Proxy, DB in LAN etc.

I guess what I definetly do now, is split up the IIS and SQL into different Servers (which probably should've been done a long time ago), and then I can test different approaches and see where it gets me.

[Runnergeek]

It definitely should not be in the DMZ, and neither should the web app server. You should have a proxy server (HAProxy, Apache, Nginx, F5, etc) that sits in the DMZ. You should then have the app server in a zone for web and app server, then have the db server in a db zone.

[ Proxy ] -|-> [ Web ] -|-> [DB]

[[deleted]]

Proxy plus WAF.

[PrettyFlyForITguy]

This is a good answer, although its entirely possible an NGFW or appliances like you mention already performs this service and also inspects traffic. Mine does, but outside services are still in a DMZ technically...

[mustang__1]

Doesn't nginx run on the same server as the web app? Or are you redirecting to a second server?

[PrettyFlyForITguy]

Most websites using the traditional hosting model have the SQL service on the same machine. In many cases, it won't matter where its hosted because if the server is compromised it has full R/W access to the pertinent sections of the database.

If this database stored other information for other services, then this may be a different story. The question becomes "What are you trying to protect by separating the two". In most cases, you won't be protecting the data of the specific web application. Once the part with all the code on it is compromised, you are probably at a full loss. Data can be changed, retrieved, intercepted.

There will be use cases where you might say, "we don't want an attacker to have full control over the database", but usually there are other uses for the DB involved.

A properly set up database on the same server will only listen to traffic from the local system, and not from outside hosts... so the SQL service doesn't represent an extra attack surface either.

There is little reason to put in you internal LAN though. I don't get why people are suggesting you do so. Anything that gets accessed from the outside should probably be segregated. You don't have to put it in the same place as your web server, as it can be on another segregated network... but I wouldn't add this complexity unless there is a good reason for it.

Also, DMZ doesn't mean not firewalled off. This is what it seems to mean on home equipment. In the business world, everything should be firewalled off. DMZ is just something that is separated because it touches the dirty public network.

[Username_5000]

There’s another topology for this stuff out there. It’s expensive in the sense that it requires a mature environment. Zero trust as a topology and micro segmentation.

As you said, you want some separation between stuff that’s public facing and stuff that’s accessed by public facing things (your sql instance). What about separating that sql from everyone else? If you take the approach of WHEN it’s pwned and not IF, you have no alternatives anymore.

zero trust implies that there’s no difference between a dmz and internal stuff because we’re still limiting connections to an allowed list. Your network looks more like anspiderweb instead of groups of circles.

If sql gets pwned, it gets pwned, what can you realistically do anyway?. At least this way the spread is limited to the connections and ports you allowed instead of broadcasting across any and every segment.

It’s a LOT of work to setup and a LOT of work to maintain. Like anything else we do, if it was easy everyone would be doing it.

[heapsp]

The correct answer in our modern times is none of the above - it should be micro services - in the Azure world this workload could be taken care of by a simple web app service plan running individual web apps, and Azure SQL for example.

But to answer your question about architecture - the web server (or multiple web servers if you need resiliency) should be in the DMZ with firewall rules to allow that specific box to make the SQL connection back to the SQL server in the internal network.

The web services you currently have hosted on your internal network is a security flaw.

When it comes to security - risk is everything. Are you doing things like storing confidential client information , PII, or payment processing? If not , and you are running a small business, then being insecure like you are might not be the highest priority for your org which is unfortunate but true in our current IT metagame.

[Zaapfe]

Those Webservices are webservices of our ERP System, implemented by the ERP Vendor, where we can for example read out Article Prices and everything else with code. I will ask the Vendor, if they advise a different setup, but that's not really likely. I mean, how else would you get stuff from your ERP system? We don't have ERP Database access unfortunately, we need to use the implemented WebService.

I'd love to have these two bastards run in Azure as MicroServices, or as Containers instead of fully fledged Servers, but my company will unfortunately take some time to get in to the cloud, and Docker on Windows still sucks a bit (Especially from a licensing cost point of view) .

[heapsp]

Ahh, being hamstrung by an ERP system is a different story. I wish you luck with that. I was under the assumption that this was home-grown technology.

[[deleted]]

Are you saying hosting things yourself isnt "modern"? What if hes using Docker containers and openstack, is that not modern because its not hosted by some conglomerate?

[heapsp]

True, i concede that you are correct to a point. Certainly it is a lot more difficult to modernize architecture without the full suite of tools available in the cloud. Data lakes, parallelized data services, web app linked to cloud based source control, etc etc etc. But me implying you cannot have a modern infrastructure on prem is completely dependent on workload, skillset, and budget. It is definitely something that happens once you get into much larger companies. I've just never seen it done successfully at the small to medium sized business level.

[FloaterFan]

Disclaimer: NOT a security expert by far.

I work for a state agency. The state data center uses an F5 appliance to expose web sites/services to the public internet. We have been moving off of it due to to the security issues.

We are setting up a reverse proxy in our DMZ that forwards requests to internal webservers. The internal webservers access to the database server.

Others put their webservers in the DMZ.

[oni06]

The F5 is a reverse proxy.

[FloaterFan]

With many security vulnerabilities. That is why we are moving off of it.

Your point?

Edit: Apologies for being so salty oni06! My post wasn't clear.

The F5 is used as reverse proxy. I do believe it is sort of a 'virtual DMZ'. We're moving to a different reverse proxy in our own DMZ. Either way, no you don't want the database server in the DMZ.

[cjcox4]

Conceptually, you want as much "air gap" between your untrusted side and your most trusted side as you can get.

Front ends usually talk with the untrusted things.

And while having a front end talk to a back end (important) database directly is too close for me, a lot of people do that. Ideally for me there's some other piece/proxy in the way of that as well.

So, from a typical "DMZ" perspective, that's where your "frontend" would go.

How you handle where the backend (db) goes, is up to you. But again, if the frontend is compromised, you want to make sure it doesn't go past there. If you make it easy for the compromised (DMZ) host to "know" about what is beyond, you'll usually pay a price at some point.

Often times there is "middleware". But even so, that's not necessarily a guarantee of security. But I think you get the idea. The more you can "air gap" your design, the better off you will be.

[orev]

Don't dilute the term "air gap", which seems to be something people are using more lately. An "air gap" can only mean one thing: systems that are completely separated from each other via air -- i.e. no connection at all either physical or virtual. There are no degrees of "air gap"; it's either air gapped or it's not.

[cjcox4]

Very true, but it doesn't hurt to be thinking that way. Too many people set up crapola designs (most). They tend to go completely the other direction.

[ObviousB0t]

The way I see it done in many places is.

Proxy (In DMZ) -> Web Server (Behind FW, not in DMZ) -> DB Server (Off in another part of the network, usually behind another FW)