Windows – Ban IP address based on X number of unsuccessful sql server login attempts

Unfortunately you are in a bad place. We talked about this exact thing over here.

You can do this with powershell and task manager. It's probably not perfect solution, but it works quite well and i have about 100 blocked IP addresses in two months. I wrote script, that select from EventLog specified events ("audit failure"). If there are many failed logins from any IP address, then it's added to firewall rule (created manually) named "BlockAttackers" which blocks any traffic to specified ip addresses.

PS1 Script:

$DT = [DateTime]::Now.AddDays(-1) # check only last 24 hours

$l = Get-EventLog -LogName 'Security' -InstanceId 4625 -After $DT | Select-Object @{n='IpAddress';e={$_.ReplacementStrings[-2]} } # select Ip addresses that has audit failure 
$g = $l | group-object -property IpAddress  | where {$_.Count -gt 20} | Select -property Name # get ip adresses, that have more than 20 wrong logins

$fw = New-Object -ComObject hnetcfg.fwpolicy2 # get firewall object

$ar = $fw.rules | where {$_.name -eq 'BlockAttackers'} # get firewall rule named 'BlockAttackers' (must be created manually)

$arRemote = $ar.RemoteAddresses -split(',') #split the existing IPs into an array so we can easily search for existing IPs

$w = $g | where {$_.Name.Length -gt 1 -and  !($arRemote -contains $_.Name + '/255.255.255.255') } # get ip addresses that are not already in firewal rule. Include the subnet mask which is automatically added to the firewall remote IP declaration.

$w| %{$ar.remoteaddresses += ',' + $_.Name} # add IPs to firewall rule

Create task in scheduler and set trigger to event 4625 (windows login including terminal services). But you can set trigger to run e.g. twice per hour to avoid unnecessary loading the server.

and after trigger run powershell script. You must also set higher privileges to run this script, otherwise it will fail with security exception.

You can also bind this script to other security events.