Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
3  
If you're talking login attempts from the internet, I think a better solution would be to stop exposing a database server directly to the Internet. –  RobM Feb 11 '13 at 15:24
    
apologies in advance for the post formatting. i'm trying to figure it out. –  user2056045 Feb 11 '13 at 15:57
    
I tried to turn off tracking protection in case it was blocking some scripts but no change (still ignores single line breaks and code blocks). Thanks longneck for the edit to correct the layout. –  user2056045 Feb 11 '13 at 16:20

2 Answers 2

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.

share|improve this answer
    
Thanks. I'd like to continue with powershell though. –  user2056045 Feb 11 '13 at 15:48
1  
We really do prefer that answers have content, not pointers to content. This may theoretically answer the question however, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  Chris S Feb 11 '13 at 15:48

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.