help choosing best design - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

help choosing best design

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-11 : 04:16:14

A web app I've been running has lately been the victim of recurring dictionary style attacks against user accounts.

I am now logging all invalid logins in a table as seen below.

Does anyone have any input on the best way to take advantage of this information? I'm not sure how I am going to notify myself of when to take action against an abusive IP, or really the best query to write to let myself know.

Thanks very much for any input !:)

mike123

CREATE TABLE [dbo].[tblInvalidLoginAttempts] (
[attemptID] [int] IDENTITY (1, 1) NOT NULL ,
[nameOnline] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attemptDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-11 : 04:22:30

well what must happen that you do take action?

you could create a scheduled job that queries your table periodicaly and if your condition for taking action is met
let ti send you a mail.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-11 : 05:39:35

Hey Spirit,

That sounds like a pretty good idea, and probably what I will do.

Any idea on a good query to write that would determine something wierd is happening ? I've logged all the information necessary, but can't figure out what type of informative queries would be helpful to visualize whats going on.

Thanks again

mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-11 : 05:59:51

maybe this?

select attemptDate, nameOnline, IP, count(*)
from
group by attemptDate, nameOnline, IP

it depends on what kind of info you'd like to have...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources