Auditing Failed Logins – SQL Server 2008

Posted on May 28, 2010 by way0utwest

I wrote about setting up a basic server audit recently. That showed about how a server level audit is set up, but that’s a just a shell. The audit itself is just a container in which you can store various audited items. Like a SQL Server Agent job, it doesn’t do anything until you add some details.
One of the details that I think is worth adding is the failed login audit at the server level. Finding a large number of failed logins can clue you in to some hacking going on, so it can be good to log these.
To add these, you first need to add a server audit specification, which is like a job step. It’s a detail at the server level. Using the SSMS GUI, you can do this by right clicking the Server Audit Specification under Security. Select “New Audit Specification”
 ServerAudit_01
That gives you a dialog where you can add a name:
ServerAudit_02
And then select an audit to which you assign this particular detail.
ServerAudit_03
For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.
ServerAudit_04
Once this is done, your audit appears in the folder in SSMS.
ServerAudit_05
Now when someone tries to log in and can’t, you can view this in the logs:
DisableAudit_07

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Auditing Failed Logins – SQL Server 2008

  1. Maria Pia says:
    May 30, 2010 at 8:46 pm

    I created a server audit to a file and I created a database server specification in order to capture create/alter/drop events in my "prueba" database:USE [master]GOCREATE SERVER AUDIT [DDL Audit]TO FILE ( FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2RTM\MSSQL\Log\' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF)WITH( QUEUE_DELAY = 0 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '5aeeab6e-dc91-401b-99eb-44b678918ca3')GOALTER SERVER AUDIT [DDL Audit]WITH (STATE = ON);GOuse pruebagoCREATE DATABASE AUDIT SPECIFICATION [DDL Audit Database]FOR SERVER AUDIT [DDL Audit]ADD (SCHEMA_OBJECT_CHANGE_GROUP)WITH (STATE = ON)GOWell, when I execute this sentence:use pruebaCREATE VIEW VISTA_1 as select getdate() as fechaIt generates a correct entry in my file BUT…..when I execute this sentence:CREATE VIEW VISTA_2 as select getdate()SQL gave an expected error (I know) and surprising it generates a correct entry in my file too!!!!!!Idem with this sentence:CREATE VIEW VISTA_1 as select getdate() as fechaSQL gave an expected error (existing object) and surprising it generates a correct entry in my file too!!!!!!So, how can I distinguish the row corresponding to a correct CREATE VIEW???Thanks,

    Like

  2. Steve Jones says:
    June 1, 2010 at 9:55 am

    Post the note on a forum. I did see in the Connect site that there was an R2 bug about auditing entries when statements failed.

    Like

Comments are closed.