CODEWITHSUNDEEP
sqlsql-serverloggingaudit
Tad
Tad

Reputation: 517

SQL Server Audit Event Log

I'm struggling to get audit working using application logs on SQL Server 2012 based upon the various responses I've found here and Microsoft's documentation. As a test, I executed the following script in Management Studio and it reported success. However, when I deliberately fail a login, I don't see any records appearing in my OS log.

USE master
GO

CREATE SERVER AUDIT AuditDataAccess TO APPLICATION_LOG
GO

CREATE SERVER AUDIT SPECIFICATION HIPPA_Audit_Specification
FOR SERVER AUDIT AuditDataAccess 
    ADD (FAILED_LOGIN_GROUP)
    WITH (STATE = ON)
GO

ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON)
GO

Clarifying: It works if I put the output to a file instead of the OS log. If I create the object like this:

CREATE SERVER AUDIT AuditDataAccess TO FILE ( FILEPATH ='C:\SqlAudit\' )
GO

...it works and I can retrieve the data using:

SELECT * FROM sys.fn_get_audit_file ('c:\SqlAudit\AuditDataAccess_*.sqlaudit',default,default);
GO

What am I doing wrong with regard to using an OS Application Log?

Edit: If nothing, then am I just looking in the wrong place? I brought up Windows 7 EventViewer and looked in each subtree of Event Viewer (local)/Windows Logs. I also looked in Event Viewer (local)/Applications and Service Logs, but don't see an entry for SQL Server beyond the empty "Microsoft-SQLServerDataTools".

Upvotes: 0

Views: 4483

Answers (1)

Tom
Tom

Reputation: 107

It should be under Event Viewer / Windows Logs / Application, with event ID 33205.

Understanding Audit Logging in SQL Server 2008

"Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log." - SQL Server Audit

Upvotes: 0

Related Questions