Cannot start SQL Server Audit on file

Question

I have the following SQL Server Audit:

CREATE SERVER AUDIT [Omega_CA_Srv_Aud]
TO FILE 
( FILEPATH = N'C:\MS_Sql_Server_Audit\'
 ,MAXSIZE = 100 MB
 ,MAX_FILES = 3
 ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '45b8fdb5-dbe5-4550-b115-e9cac5ef327b')

When I try to enable it, I get the following error:

TITLE: Microsoft.SqlServer.Smo

Enable failed for Audit 'Omega_CA_Srv_Aud'.

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.31.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enable+Audit&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Audit 'Omega_CA_Srv_Aud' 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'. (Microsoft SQL Server, Error: 33222)

It was working before. One (MS) day it failed to start.

I have given full permissions to Everyone on the OS directory

enter image description here

... but still the same issue persists.

The disk volume is 60 Gb and 39 Gb are free.

at SQL Server Log do see the following:

Date,Source,Severity,Message
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536<c/> Initialized and Assigned State: TARGET_CREATION_FAILED
04/13/2025 23:12:57,spid61,Unknown,SQL Server Audit failed to create an audit file related to the audit 'Omega_CA_Srv_Aud' in the directory 'C:\MS_Sql_Server_Audit'. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file.
04/13/2025 23:12:57,spid61,Unknown,Error: 33244<c/> Severity: 17<c/> State: 1.
04/13/2025 23:12:57,spid61,Unknown,SQL Server Audit failed to create the audit file 'C:\MS_Sql_Server_Audit\Omega_CA_Srv_Aud_45B8FDB5-DBE5-4550-B115-E9CAC5EF327B_0_133890523773110000.sqlaudit'. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file.
04/13/2025 23:12:57,spid61,Unknown,Error: 33206<c/> Severity: 17<c/> State: 1.
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536<c/> State changed from: START_FAILED to: TARGET_CREATION_FAILED
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536<c/> Initialized and Assigned State: START_FAILED
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536 Session has been closed
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536<c/> State changed from: TARGET_CREATION_FAILED to: SHUTTING_DOWN
04/13/2025 23:12:57,spid61,Unknown,Audit: Server Audit: 65536 Session is being closed. Audit Guid: 45B8FDB5-DBE5-4550-B115-E9CAC5EF327B

Version is Developer Edition 2019

• siggemannen: Did you look into sql server error log? See blog.sqlauthority.com/2017/03/31/…
• altink: I did, and just updated the question with that info. Cannot find anything to help
• siggemannen: well, it says the disk is full, but it could also be you have someone already reading the file. Maybe close all apps that do
• CatchAsCatchCan: This is a question for Database Administrators
• Sean Gallardy: Just start procmon and make sure it's recording. Repro the failure. Stop recording in procmon and save the trace. If you don't want to share it with us (aka the internet) you can use the filter to set the path and include your path, then check the output in procmon. You can look at the CREATEFILE calls and see the result (which is most likely access denied).

Answer 1

I did resolve the problem.

It was about using "MAX_FILES = 3". The number had been reached, and no new file could be created.

I should have been using the MAX ROLLOVER files option. I did, and I could enable the server audit.

PS. The error from the SQL Server should be something more specific and less misleading. Kind of: "maximum number of audit files has been reached."

Answer 2

Check the SQL Server error log for audit failure:

EXEC xp_readerrorlog 0, 1, 'Omega_CA_Srv_Aud';

Query the ring buffer for details:

SELECT *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG'
AND record_id = (
 SELECT MAX(record_id)
 FROM sys.dm_os_ring_buffers 
 WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG'
);

Note: Check if auditing is blocked by antivirus.

• altink: just updated the question with that info. While the query has an error: Invalid column name 'record_id'.
• altink: Hi @Manish Sharma Message Target 'asynchronous_security_audit_file_target' creation failed and did not call SetLastError(). file: file create or open failed (last error: 0) Target 'asynchronous_security_audit_file_target' creation failed and did not call SetLastError(). file: file create or open failed (last error: 0) Target 'asynchronous_security_audit_file_target' creation failed and did not call SetLastError(). file: file create or open failed (last error: 0) file: file create or open failed (last error: 0) XE client initialization complete result 0.

Answer 3

For the ring buffer query try:

SELECT lines.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
 lines.record.value('(/Record/XE_LogRecord/@message)[1]', 'varchar(max)') AS [Message]
FROM
(
 SELECT CAST(record AS XML) AS record_xml
 FROM sys.dm_os_ring_buffers
 WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG'
) Tab
 CROSS APPLY record_xml.nodes('//Record') AS lines(record);

Answer 4

The error message has the following information:

...not full and that the SQL Server service account has the required permissions to create and write to the file.

If there is a file in the directory which looks similar to this:

Omega_CA_Srv_Aud_7518E344-F2C7-4633-B52F-5268579EC9C9_0_133892606078860000.sqlaudit

...ensure that the file is accessible by the SQL Server service account, aka the account under which your SQL Server service is running.

Right-click on file | Properties | "Security" Tab | "Advanced" button | Owner: <your SQL Service account>

File permissions of sqlaudit file showing file owner

The owner should be the SQL Server service account.

Possible Solutions

1. If the SQL Server service account is not the owner of the *.sqlaudit file, switch the owner to that specific account.
2. If a *.sqlaudit file exists, delete the file and Enable the SQL Server Audit again.
3. Drop the SQL Server Audit, delete any existing *.sqlaudit file and re-create the SQL Server Audit with your script. Enable.

Possible Causes

• The account which runs the SQL Server service was/has changed. As a result the permissions assigned to the file during initialization are no longer valid.
• The permissions on the existing *.sqlaudit file have been modified. No, EVERYONE is not always a solution.

Observations

During the re-creation of your issue on a test instance, I did not have to assign the EVERYONE any rights/permissions to the directory. I just manually created the directory and it then had some default rights. Only after enabling the SQL Server Audit did the file itself have permissions assigned: Owner: <SQL Server Service account>.

Good luck.