Auditing Failed Logins

Hi,

I need to be able to get info about failed login attempts which need to be running all the time and logging info such as hostname , application name, host machine, etc. The best way I can see to do this is by crerating a trace. Then loading the info from the trace into a table for later use by using fn_trace_gettable. But this requires that the trace be stopped and the definition removed for the trace file to be populated.

Is there a way to be able to log this info constantly into a table without having to stop and then recreate another trace. Also it requires that the table created by the function is dropped.

Anyone any idears?

Thanks

Hello,

What version of SQL Server are you using? If 2005, you have the option of using server-level triggers.

Cheers,

Rob

|||

Hi,

Thanks for your reply. We are using 2000.

Thanks

Bob

|||

With 2000, unless you are using a 3rd party product, then you can only get that information with Profiler. There's a reason that you don't see that information with SQL Server though: it's so easy to spoof/change the hostname/appname information that it's a joke. Those are just ADO connection string values; you can set them to anything you want so I wouldn't want to make critical decisions like "Should I fire Bob over his failed logins?" over it.

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

|||

Hi Bob,

Actually, if this information is crucial, you can enable C2 auditing on the mssql instance...although this should not be done without a thorough understanding of the concequences (performance being one of those considerations).

Have a look at the below links to get started:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx

https://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

Cheers,

Rob