Learning can be a fun and exciting journey, especially when we encounter challenges that push us to expand our knowledge. Recently, while working on a blog about auditing in SQL Server, I came across an interesting error that I would like to share with you. Let’s dive into the topic of Audit Failed to Start Error 33222 and explore its causes and solutions.

Here are the commands I was trying to run:

USE MASTER
GO
-- Create the server audit
CREATE SERVER AUDIT MyAudit
TO FILE (FILEPATH ='E:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Audit');
GO
-- Enable the server audit
ALTER SERVER AUDIT MyAudit 
WITH (STATE = ON);
GO

In my case, I was working with a SQL Server 2016 instance and had created a folder called “Audit” in the default MSSQL directory. However, as soon as I executed the second command, I encountered the following error:

Msg 33222, Level 16, State 1, Line 8
Audit 'MyAudit' 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'.

What I appreciate about this error message is that it provides guidance on what to do next. If you are new to SQL Server and unfamiliar with the ERRORLOG, I recommend reading the blog post titled “SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location.”

Upon further investigation, I found the following entries in the SQL Server error log:

2017-03-07 05:20:53.36 spid54 Audit: Server Audit: 65537, State changed from: TARGET_CREATION_FAILED to: SHUTTING_DOWN
2017-03-07 05:20:53.36 spid54 Audit: Server Audit: 65537 Session has been closed
2017-03-07 05:20:53.36 spid54 Audit: Server Audit: 65537, Initialized and Assigned State: START_FAILED
2017-03-07 05:20:53.36 spid54 Audit: Server Audit: 65537, State changed from: START_FAILED to: TARGET_CREATION_FAILED
2017-03-07 05:20:53.36 spid54 Error: 33206, Severity: 17, State: 1.
2017-03-07 05:20:53.36 spid54 SQL Server Audit failed to create the audit file 'E:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Audit\MyAudit_F141FF1E-8041-48E9-BC2E-27FA3BA90213_0_131333178533700000.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.
2017-03-07 05:20:53.37 spid54 Error: 33244, Severity: 17, State: 1.
2017-03-07 05:20:53.37 spid54 SQL Server Audit failed to create an audit file related to the audit 'MyAudit' in the directory 'E:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\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.
2017-03-07 05:20:53.37 spid54 Audit: Server Audit: 65537, Initialized and Assigned State: TARGET_CREATION_FAILED

From these log entries, it became clear that the SQL Server service account did not have the necessary permissions to write to the specified folder. To confirm this, I queried the sys.dm_os_ring_buffers view using the following query:

SELECT *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG'

The result of the query indicated that error code 5 corresponds to an access denied error, which aligns with the information provided in the ERRORLOG message.

So, what is the solution or workaround for this issue? Here are the steps:

1. Make a note of the service account used by the SQL Server service using the configuration manager.
2. Provide full control permissions to the folder where you want to write the audit file. In my example, it was ‘E:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Audit’.
3. To grant permissions, right-click on the folder, go to properties, then security, and add the service account.

By following these steps, you should be able to resolve the Audit Failed to Start Error 33222 and successfully create and enable your server audit.

Remember, encountering errors and finding solutions is an essential part of the learning process. Embrace these challenges, and you’ll become a more proficient SQL Server user.