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”
That gives you a dialog where you can add a name:
And then select an audit to which you assign this particular detail.
For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.
Once this is done, your audit appears in the folder in SSMS.
Now when someone tries to log in and can’t, you can view this in the logs:
Auditing Failed Logins – SQL Server 2008
Currently Reading
administration AI Azure Backup/Recovery career career2 car update Cloud Computing coping database design databases Database Weekly DevOps disaster recovery encryption Friday Poll goals hardware Humor life Microsoft misc PASS powershell Redgate republish sabbatical security software development software development speaking SQL in the City SQLNewBlogger SQL Prompt SQL Saturday sql server syndicated T-SQL T-SQL Tuesday Tesla travel vacation windows words work
Voice of the DBA 
Comments
2 responses to “Auditing Failed Logins – SQL Server 2008”
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,
LikeLike
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.
LikeLike