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

Question

Is it possible to expand on the idea presented in

Ban IP address based on X number of unsuccessful login attempts?

to encompass IP addresses for repeated failed sql server login attempts?

There are log entries in the Application log for each failed attempt. Source = MSSQLSERVER EventID = 18456 and 18452

EDIT 1:In Michael Khalili's response, the #Select Ip addresses that has audit failure powershell section could be replaced by:

#Select Ip addresses that has audit failure
$l = Get-EventLog -LogName 'Application' -InstanceId 3221243928 -After $DT | Select-Object @{n='CLIENT';e={$_.ReplacementStrings[-1]} }

This gives a list from the application log but isn't in a similar format leading into the #Get ip addresses, that have more than x wrong logins powershell section.

I'm not confident the selection is proper.

Answer 1

Check out ts_block. I use it on my win2k3 server to block multi failed ssh attempts and it works great. Just modify config for remote MSSQL connections.

Answer 2

I think I solved this...I get the previous idea, your line to retrieve the data and performed some mod...here is...

(And sorry for my dirty code..It was my 1st time working with PS)

$DT = [DateTime]::Now.AddDays(-1) 

$l = Get-EventLog -LogName 'Application' -InstanceId 3221243928 -After $DT | Select-Object @{n='CLIENT';e={$_.ReplacementStrings[-1]} }
$g = $l | group-object -property CLIENT  | where {$_.Count -gt 10} | Select -property Name

$fw = New-Object -ComObject hnetcfg.fwpolicy2 

$ar = $fw.rules | where {$_.name -eq 'Blocks'} 

$arRemote = $ar.RemoteAddresses -split(',') 

$w = $g | where {$_.Name.Length -gt 1 -and  !($arRemote -contains $_.Name + '/255.255.255.255') } 

$x = $w.Name.Replace(" [CLIENT: ","")

$z = $x.Replace("]","")
$h = ""
for($i=0;$i -lt $z.Count;$i++)
{
    $h += $z[$i]
    $h +="/255.255.255.255"
    $h += ","
}
$full = $h + $ar.remoteaddresses
$fullarray = $full.Split(',') 

$newlist = $fullarray | select -uniq

$ips = "";
for($i=0;$i -lt $newlist.Count;$i++)
{
    $ips += $fullarray[$i]
    if($i -lt ($newlist.Count -1))
    {
        $ips +=","
    }
}

$w| %{$ar.remoteaddresses = $ips} # add IPs to firewall rule