Protecting your SQL Server with Windows Firewall – Part 1

Having read a great blog post “The Ransomware Breach You’re Going to Have” by Joey D’Antoni (t|b) last year and revisiting it again recently it got me thinking about network segmentation. While Joey covers the core principals of network segmentation and where traffic can and cannot go, we need to think about what happens once something is within one of those boundaries and how we limit lateral movement between members of the network segment.

Off the back of this I decided to run a very unscientific and statistically insignificant Twitter poll about what people do for this. The results were actually somewhat better than I had been expecting:

Twitter Poll results
Figure 1. Twitter Poll Results

I’m encouraged to see that just over two thirds of respondents use something to lock down their server-to-server traffic. However, this does leave nearly one third who are not using anything.

“But why should I bother, I have a network firewall” I hear you say. Well, because network firewalls are typically used to filter traffic between network segments, rather than traffic within them. There are several micro-segmentation options on the market including VMware NSX which help control lateral movement.

Thinking about NotPetya and how it moved so rapidly between systems it is clear that one of the main issues was poor security hygiene in the environment. The “Everything you need to know about the Petya, er, NotPetya nasty trashing PCs worldwide” article from The Register helps highlight some of just how brutal this ransomware is. While there are vulnerabilities at play here, not locking down what is acceptable traffic between systems allows the attack to propagate.

By closing down and preventing this traffic from getting into the machine it solves many problems before they can bite.

Good Traffic Vs. Bad Traffic

Let’s think about a very basic deployment scenario.

Network diagram depicting three subnets communicating through a firewall.
Figure 2. Simple Network Configuration.

Looking at the diagram in Figure 2. we can see three distinct network segments for web servers, management servers, and database servers. When traffic is transiting between these systems it passes through the firewall. Here we can specify source and destination systems with port numbers. This lets us make sure that only the required network traffic gets to the right place from a trusted source.

For example, traffic from the web servers is limited to the specific database server on the port for the database engine. No SMB, WinRM, etc. is permitted from that server to the database servers. Likewise, the database servers are not permitted to communicate with anything outside of their network segment, but can receive management traffic for WinRm, SQL Server etc. from the management server.

Note: It is my experience that people will push hard to have direct access to servers from all sorts of places. Preventing this free for all presents other issues such as needing to have secure access points etc. But that discussion is for another time.

The bit that we need to be more worried about is called out in the red arrows between the database servers. This traffic by its nature will not pass through the main network firewall. Preventing bad traffic transiting between servers here requires another layer in our defence and is where the likes of NSX or Windows Firewall come into play.

Windows Firewall to the Rescue

Windows firewall gets a bad rap when it is in fact a very solid piece of work. It has been held back by not really having a good enterprise management interface early on. However, with PowerShell cmdlets and DSC it is now a lot more feasible to enable this and protect your servers.

Microsoft provide a handy guide in their documentation “Configure the Windows Firewall to Allow SQL Server Access“. While this is good at explaining the options, in reality I would recommend that we get far more specific on where the traffic is coming from, ports, etc. for our rules. In reality, SQL Server systems should not really have many sources trying to connect to them unless you have a very specific scenario (there is always an edge case).

So, coming back to the third of people who are not using anything at the moment. Should we just go and enable the firewall and be done? Well, it depends. Do you know everything that is communicating with your SQL Server system so that you can set the explicit rules as opposed to the more permissive? If you do, then great go for it (later in this series of posts I’ll be covering how to manage Windows Firewall at scale). If not, then you need to do some homework first so that we don’t break stuff.


I hope that I have got you thinking about how well protected your SQL Server systems are. We need to be vigilant and really understand that defense in depth is how we protect our systems. While many use the analogy of an onion for security I much prefer to compare the layers to swiss cheese. Each one has a hole in it, and we need to have multiple layers that compliment and cover the holes in the layers above and below.

While I have described at a high level here what we need to try and achieve I have not described the how. In the next post I will cover how to capture details of the traffic that is connecting to our SQL Server systems and then generate our rules from that.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.