Sql-server – get error messages from SQL Server Auditing

auditsql serversql server 2014

Where can I get information about the audit process failing to start?

I'm setting up auditing on my MS SQL 2014 server. I've created an AUDIT and an associated AUDIT SPECIFICATION, but when I try to enable the audit, I get this message:

Msg 33222, Level 16, State 1, Line 13
Audit 'X' failed to start. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

Checking the error log, I see:

Audit: Server Audit: 65552, Initialized and Assigned State: START_FAILED

That's about as vague as one can get. The 65552 number is just an auto-incrementing value, not an error code.

OK, how about that dm_os_ring_buffers table? When filtered for the ring_buffer_type mentioned in the original error, I get nothing. Well, there are a couple hundred messages, but none are new and there's no change after each attempt to start auditing. Without a filter, I get an XML message, with these salient points:

Record type: RING_BUFFER_EXCEPTION
Task address: 0x000000189D6244E8
Error: 33222
Severity: 16
State: 1
UserDefined: 0
Origin: 0

According to sysmessages, this error code means:

Audit '%.*ls' failed to %.*ls. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

There's a joke in here somewhere about ring buffers and going in circles.

It's possible that the service account doesn't have privs to write to the system event log, but I've also tried setting up my audit to use the application log or a folder, and there's been no change. Also, according to another question, if that was the case it should be mentioned in the error log.

The file path I selected is a subdirectory of the database's log folder, and the service has full rights by inheritance, so it should have no problem reading or writing.

Here's the script for the AUDIT alone; the error occurs when trying to activate the audit.

CREATE SERVER AUDIT X
    TO FILE (FILEPATH = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\', MAXSIZE = UNLIMITED)
    WITH    (ON_FAILURE = CONTINUE)
GO
ALTER SERVER AUDIT X WITH (STATE = ON)

Best Answer

Related Question