Protecting your SQL Server with Windows Firewall – Part 2

In part 1 of this series we looked at why we should be protecting servers with a firewall from traffic within their network segment. Here we are going to look at how we go from having nothing in place to enabling Windows firewall and not prevent legitimate traffic from being received.

So, just how do we go from nothing to having another layer of protection?

Doing Your Homework

Before we can turn anything on, we need to understand what is actually talking to our servers right now. There are many ways to do this, and in my experience works best when you collaborate with other teams. First and foremost, talk to the network team and ask them to start tracking traffic through the network firewalls to your specific servers.

But this is only part of the story as I mentioned earlier. Now we need to start logging traffic we see on our servers. This is where Windows Firewall can help us out, but we need to make sure that we do it in the right way. Otherwise we will end up blocking traffic and have a problem on our hands.

Step 1 – Setup a Permissive Inbound Rule

Note: This should only be used to help when logging where you have the firewall disabled and not preventing traffic. Please do not put this in place on your servers as a matter of course as it will nullify any protection you have.

Because we are looking for inbound traffic to our servers, we need to create a windows firewall rule to let anything in. This in effect simulates the firewall being off but allows us to then configure logging.

This can be done via the Advanced Settings in the “Windows Defender Firewall” control panel option. Alternatively, you can use the following PowerShell with New-NetFirewallRule:

$FirewallRuleParams = @{
    Name = "AllowAll-In"
    DisplayName = "Allow All In"
    Description = "Rule to allow all inbound traffic for logging purposes."
    Action = "Allow"
    Direction = "Inbound"
    Profile = "Domain"
    Enabled = "False"
$Rule = New-NetFirewallRule @FirewallRuleParams

A couple of things to note about this rule:

  1. It allows everything into our server.
  2. It is disabled by default; we will enable it when we are ready.
  3. It is restricted to the Domain network profile only.

Point three is an interesting one and worth a little more explanation. There are three network profiles available to us, Domain, Private, and Public. I’m working on the assumption that this is a domain joined server so that is where our traffic comes from. If you have additional networks configured under different profiles, then you will need to add those to this rule too. The reason that I restrict this to domain traffic only is that in the event a VM admin or whoever adds a network card by mistake then I’m not allowing traffic from random places.

Step 2 – Configure the Logging

Once we have our rule in place, we need configure the logging for traffic reaching the server. This is done based on the network profile so you will have up to three different log files depending on your configuration.

Again we can configure this with PowerShell using Set-NetFirewallProfile; the following will configure the Domain profile to log to a file in the specified directory.

$MaxLogSizeMB = 32MB
$FirewallLoggingParams = @{
    Profile = "Domain"
    LogFileName = 'L:\Logs\Firewall\DomainProfile.log'
    LogAllowed = "True"
    LogBlocked = "True"
    LogIgnored = "True"
    LogMaxSizeKilobytes = (($MaxLogSizeMB/1KB)-1)
Set-NetFirewallProfile @FirewallLoggingParams

There are a couple of things you should note here. First, the maximum size of the log file is just under 32 MB and the parameter only accepts Kilobytes not Bytes. Second, while it is possible to have a single log file for all profiles I am breaking them out to one per profile. This is partly because of the 32MB limit, but also because the log file does not contain which profile the log record was from. While it is possible to parse the log and work out which profile based on IP addresses it is simpler to use separate log files.

Step 3 – Turn it all on

Now that we have the rule in place and the profile configured to log information all we need to do is turn it all on. Again we can use PowerShell for this to make it nice and easy with Set-NetFirewallProfile and Set-NetFirewallRule.

$EnableFirewallRule = @{
    Name = "AllowAll-In"
    Enabled = "True"
Set-NetFirewallRule @EnableFirewallRule

$EnableDomainProfile = @{
    Name = "Domain"
    Enabled = "True"
Set-NetFirewallProfile @EnableDomainProfile

Now that this is enabled our log file should be filling up with information about what network traffic is entering our system via the domain profile. If you have other networks active or just want to check if anything is coming in via the other profiles repeat the steps above to them too.

Now What?

There are a couple of things that we need to think about:

  1. How long do we want to capture data?
  2. How are we going to process this data?
  3. What will we do with the insight?

For the first two points I have to say it is very much an “it depends” response. I would always say that capturing a business cycle is important to make sure that you capture infrequent processes. Think about monthly, quarterly, and annual processes. While waiting a whole year is not really feasible, typically monthly processing is a smaller form of yearly so we can project out and ask the business from there.

As for how to process the data, pick your tool of choice and run with whatver you’re comfortable with. There is no one right answer here. One thing that I will say is that you will need to copy out the log file on a regular basis. We only have 32MB (nearly) to play with and that can cycle fast on a busy system. Again, use whatever you are comfortable with when it comes to this.

When it comes to what we are going to do with this insight, we are going to build a robust set of firewall rules to protect our server. That’s what we are going to do…


After explaining why we need to think about protecting our servers from network traffic in the same subnet, we have now looked at how we go about collecting the raw data we need from the Windows firewall to help us understand the inbound traffic to our servers.

In my next post we are going to look at how to process the log file and use the data to build those all-important firewall rules.


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.