You are here: Saotn.org » Windows Server » Block brute force attacks on SQL Server, block IP addresses in Windows Firewall using PowerShell

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

This PowerShell solution blocks IP addresses that are trying to brute force your SQL Server logins, by blocking IP addresses in Windows Defender Firewall with Advanced Security. 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]
Code language: YAML (yaml)

Protip: 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.

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.

Note: 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}")
Code language: PowerShell (powershell)

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}")
Code language: PowerShell (powershell)

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
Code language: PowerShell (powershell)

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
Code language: PowerShell (powershell)

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
Code language: PowerShell (powershell)

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
Code language: PowerShell (powershell)

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

Leave a Comment

Your email address will not be published. Required fields are marked *