Block brute force attacks on SQL Server, block IP addresses in Windows Firewall using PowerShell

For the time being, some manual labor is involved, but it is still manageable. You can use this to create your own solution to block offending IP addresses in SQL Server's firewall.

Microsoft SQL Server logs failed login attempts in SQL Server Logs, which practically is the ERRORLOG file in your SQL Server Log directory. An failed login attempt is for example:

2021-09-16 00:21:04.95 Logon       Error: 18456, Severity: 14, State: 8.
2021-09-16 00:21:04.95 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 233.252.0.12]

When writing blog posts, substitute real-world IP addresses with reserved ones. Best practice is to use Documentation scope IP addresses in net blocks 192.0.2.0/24, 198.51.100.0/24, 203.0.113.0/24 and 233.252.0.0/24.

Manually failover all databases in an SQL Server Database Mirroring configuration

In order to protect your SQL Server from these brute force attacks, you need to block this IP address. "OK, that's easy", you might think. But what if there are thousands of log lines? Let's use PowerShell to automate parsing this log and filtering IP's to block.

In a nutshell, you're going to use PowerShell to:

• parse SQL Server ERRORLOG log file
• get all IP addresses responsible for failed login attempts
• filter out your own IP addresses (you don't want to lock yourself out)
• add those IP's to the Windows Defender Firewall with Advanced Security. If it's not listed yet, that is.

Regexes I use in these examples are probably not strict enough, use at your own risk.

First you define a simple IP pattern regex:

$ipPattern = [Regex]::new("\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}")

Secondly, define a regex matching your IP space and localhost (to filter out):

# My network lives in 203.0.113.0/24
$own_IPs = [Regex]::new("(127\.0\.0\.1|203\.0\.113\.[0-9]{1,3}")

You use these two regexes to filter SQL Server's ERRORLOG. The following gets the contents of ERRORLOG, selects all IP addresses and filters out your own IP addresses / space. It's added to $result array:

$result = gc D:\mssql\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG | Select-String ${ipPattern} | Select-String -notmatch $own_IPs

ASP and ASP.NET connection string examples for Microsoft SQL Server and MySQL

How to add, list and remove IP addresses in Windows Firewall

If you want to know the contents of $result, you can get all array values with $result.Matches.value. For the time being, write it out to a text file:

$result.Matches.value | Out-File ~/ips.txt

All IP addresses are now in the text file ips.txt, in your home directory. But you need unique IP's:

Get-Content ~/ips.txt | Sort-Object | Get-Unique -AsString | Out-File ~/unique_ips.txt

Yeah, yeah, of course you can simplify this. For the sake of this post, it's as verbose as possible :-)

Now you can easily loop through your unique_ips.txt file using PowerShell's foreach. Add IP's to an array, filter out already blocked IP's, and feed that array to New-NetFirewallRule. Sounds easy, right? Here it is:

$ips = @()
foreach ($ip in Get-Content .\unique_ips.txt) {
  if ((Get-NetFirewallRule -DisplayName "IP Block SQL Server" | Get-NetFirewallAddressFilter).RemoteAddress -eq $ip) {
    # debug:
    # Write-Host "IP ${ip} already blocked"
    continue
  }
  else {
    $ips += $ip
  }
}

New-NetFirewallRule -DisplayName "IP Block SQL Server" -Direction Inbound -Action Block -RemoteAddress $ips

If you do this (twice-) daily, you can keep your SQL Server pretty safe from brute force attacks. Or better: automate this.