SQL SERVER – How to Chang Audit Location?

The auditing capability inside SQL Server is a hidden gem and not known to many in my opinion. If you have a requirement to audit your SQL Server environment and want to keep track of the activities such as DBCC commands on the server, backup, restore, failed logins, login creations, database access, database permission changes, user password changes, trace changes and much more can be audited at the server node. A similar longer list is available at the database audit node too which we can use. In this blog post let me talk about a simple task of changing the location of the audit log after it was created.

This post is inspired by one of the sessions I attended where one of the attendees had asked, if we can audit and store the information of audit on a network share. What will happen when the network share is unavailable because of network issues? Is there a seamless way to switch or change the network location anyhow? This was an interesting question and I do know from SQL Server 2012, the concept of audit resilience is available. Moreover we can also change the share which holds the Audit information using TSQL.

In this blog post we will go in sequence below:

1. Create an Audit on a network share
2. Enable the Audit
3. Create an Server Audit Specification
4. Enable the Server Audit Specification
5. Look at the metadata and access the Logs
6. Create an failure by removing sharing the Network drive
7. Change the Audit location to a local drive
8. Access the Audit logs

So let us go through the scripts for the above steps:

-- Step 1 - Create an Audit on a network share
CREATE SERVER AUDIT [NetworkAudit] TO FILE(FILEPATH='\\SQLAuthority\Audit\Network') /* substitute in here network drive */
WITH (ON_FAILURE=FAIL_OPERATION, QUEUE_DELAY=0);

-- Step 2 - Enable the Audit
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=ON);


-- Step 3 - Create a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION [complianceServerSpec] FOR SERVER AUDIT [NetworkAudit] ADD (SCHEMA_OBJECT_ACCESS_GROUP);


-- Step 4 - Enable the Server Audit Specification
ALTER SERVER AUDIT SPECIFICATION [complianceServerSpec] WITH (STATE=ON);


If you ask me these are the most common steps we will take to create ANY audit. As outlined above, we have created a server audit where we are auditing any Schema Object Access.

-- Step 5 - Look at the metadata and access the Logs
SELECT * FROM sys.server_file_audits
SELECT * FROM sys.fn_get_audit_file('\\SQLAuthority\Audit\Network\*', NULL, NULL);
SELECT * FROM sys.dm_server_audit_status


Let us simulate an error on the network path where the audit files are located. Check the metadata where the filesize if 0. That indicates we have a lost network location.

-- Step 6 - Create an failure by unsharing the Network drive
SELECT * FROM sys.dm_server_audit_status


If we try to read the file location we will get the following error:

SELECT * FROM sys.fn_get_audit_file('\\SQLAuthority\Audit\Network\*', NULL, NULL);

Msg 33224, Level 16, State 4, Line 20
The specified pattern did not return any files or does not represent a valid file share. Verify the pattern parameter and rerun the command. 

Since this network drive is unavailable, we need to move the auditing to a local drive. Here is how we can do the same.

-- Step 7 - Change the Audit location to a local drive
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=OFF);
ALTER SERVER AUDIT [NetworkAudit] TO FILE(FILEPATH='D:\Audit');
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=ON);


In this example I am moving the drive to a local D:\ drive. At many times it is quite possible we might not have access or permission to write to a local folder on the server. If that is the case, we are likely to get the following error when we try to enable the STATE=ON.

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

If we query the dm_os_ring_buffers, it will mention the file creation error. This is because we do not have enough permissions to write on the folder. Go ahead and change the permissions appropriately and rerun the command.

<Record id = “443” type =”RING_BUFFER_XE_LOG” time =”4084184″><XE_LogRecord message=”file: file create or open failed (last error: 5)”></XE_LogRecord></Record>

There is yet another error that can come when configuring the location to a local server. If the folder specified in the ALTER SERVER AUDIT is not, accessible or not available, SQL Server will raise an error as:

Msg 33072, Level 16, State 1, Line 2
The audit log file path is invalid.

So make sure the folder specified exists before issuing the command. Let us check the metadata to see if the changes have taken effect:

-- Step 8 - Access the Audit logs
SELECT * FROM sys.dm_server_audit_status
SELECT * FROM sys.fn_get_audit_file('D:\Audit\*', NULL, NULL);


The output would look like, take a note of file size to be non-zero:

This concludes the logical steps one needs to do to setup and change the audit file location inside SQL Server. As a final step, here are the steps to clean up the objects created in this blog post.

-- Cleanup
ALTER SERVER AUDIT SPECIFICATION [complianceServerSpec] WITH (STATE=OFF);
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=OFF);
DROP SERVER AUDIT SPECIFICATION [complianceServerSpec];
DROP SERVER AUDIT [NetworkAudit];


Reference: Pinal Dave (https://blog.sqlauthority.com)