query help - brute force attack

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query help - brute force attack

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-05-10 : 20:04:17

Hello,

I am just having a little brain freeze here. I am getting my database hit with a brute force attack. The passwords aren't completely strong, so there is some room to play.

I lock accounts after multiple attempts on an account but what is happening is a simple password is being tried across multiple accounts until a match is made. There are about 1 million accounts to choose from, so some success is made here and there.

Everything is logged and I can see I am being hit from about 150 different IP addresses.

I would like to query what IP addresses have attempted to loging to multiple accounts, but am a bit razzled and can't think how to write this.

table structure is

attemptID, nameOnline, password, IP, attemptDate

How can I query a list of IP's that are attempting brute force attacks?

Any help is hugely appreciated !

Thanks!
Mike

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-10 : 20:34:06

If you want to see IP addresses from which multiple nameOnline's have tried to log in:


select 
   IP, count(distinct nameOnline)
from
   YourTable
group by
   IP
order by
   2 desc

If you want to see a user name that has attempted logins from multiple IP's


select
   nameOnline, count(distinct IP)
from
   YourTable
group by
   nameOnline
order by
   2 desc

You may also want to add a where clause to look only for only recent attempts. For example, if you wanted to look for attempts in the last 24 hours:


....
from
   YourTable
where
   attemptDate > getdate()-1
group by
   nameOnline
....

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-11 : 01:40:54

Once you've found the list, block them at the firewall. That's what it's there for.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources