SQL Server Auditing not writing to Logfiles

Question

Post reply

SQL Server Auditing not writing to Logfiles

_Beetlejuice

Ten Centuries

Points: 1064
More actions
September 28, 2010 at 7:03 am

#229851

Hi
I am setting up auditing on a database table for inserts updates and deletes. The problem I have is that when I try and test the audit by perfroming an update on the table - nothing writes to the file. I know that the permissions are OK as it will write other audit events such as changing the audit (I have now removed these from my database audit).
I have done this successfully on my test environment, but when i am trying to get this working in live, there are no events.
The kind of statement I am trying to generate an event is:
update dbo.dtEvent_ecb00bfe_515f_45cb_af3d_612f25b05060
SET EventNo = 46287 where Id = 2
Below is the scripted copy of the Audit, Server Audit Specification and Database Audit Specification.
Any help would be greatly appreciated.
Best Regards
Chris
USE [master]
GO
/****** Object: Audit [SCOM_AUDIT] Script Date: 09/28/2010 13:46:05 ******/
CREATE SERVER AUDIT [SCOM_AUDIT]
TO FILE
(FILEPATH = N'C:\audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '20b93785-8eb7-4308-b125-d1c191dbfb34'
)
GO
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [SCOM_SERVER]
FOR SERVER AUDIT [SCOM_AUDIT]
ADD (DATABASE_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO
USE [OperationsManagerAC]
GO
CREATE DATABASE AUDIT SPECIFICATION [SCOM_DATABASE_AUDIT]
FOR SERVER AUDIT [SCOM_AUDIT]
ADD (AUDIT_CHANGE_GROUP),
ADD (UPDATE ON OBJECT::[dbo].[dtEvent_ecb00bfe_515f_45cb_af3d_612f25b05060] BY [***\*****)
WITH (STATE = ON)
GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736578 More actions · Answer 1

Did you enable the server audit? I believe this is disabled by default and you need to specifically enable your audit

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 2

Yes...I enabled the audit, the server audit specification and the Database Audit Specification.

Best Regards

Chris

Steve Jones - SSC Editor SSC Guru Points: 736578 More actions · Answer 3

Does the service account have rights to c:\audit? Any errors in the log?

I'll try to dup this and see if I can see anything wrong with the scripts.

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 4

The service account has access to write to the file. It does actually write to the file if I change the audit and add another audit action type for instance.

Unfortunatly, there are no errors in the sql server or sql agent logs.

Steve Jones - SSC Editor SSC Guru Points: 736578 More actions · Answer 5

Strange. In a test database, I get this from your script:

Msg 105, Level 15, State 1, Line 4

Unclosed quotation mark after the character string '***\*****)

WITH (STATE = ON)

GO

'.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '***\*****)

WITH (STATE = ON)

GO

'.

You seem to be missing a ] in the script below. Did you cut paste this? Or is this generated from SSMS, or is it a custom script? I can't get *\* to work.

If I add an audit specification

CREATE DATABASE AUDIT SPECIFICATION [SCOM_DATABASE_AUDIT]

FOR SERVER AUDIT [SCOM_AUDIT]

ADD (UPDATE ON OBJECT::[dbo].[dtEvent_ecb00bfe_515f_45cb_af3d_612f25b05060] BY [public])

WITH (STATE = OFF)

GO

It works for me.

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 6

sorry about that...I edited the script to remove the domain\user I had added.

I did try using public before, that did not work either.

Steve Jones - SSC Editor SSC Guru Points: 736578 More actions · Answer 7

If you drop and add this back, does it work?

This should be very simple, and I've yet to run into a place where it doesn't just record stuff in testing. Is there any other audit action on this object?

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 8

I am back at home now and have tested it again there, it seems to work with no problem.

The only thing that I can think that may be a problem is that we upgraded the edition of the instance at work from sql server 2008 standard to sql server 2008 enterprise. Could that possibly have anything to do with it?

Steve Jones - SSC Editor SSC Guru Points: 736578 More actions · Answer 9

I guess it's possible. My guess is something just flaked with this particular audit specification and maybe messing with it (enable/disable, drop, alter, etc.) might have "fixed" it.

It's possible that you might have missed something small, but I'd chalk it up to a rare error and keep an eye on it. If it continues to work, likely it was a one-off situation.