The auditing capability inside SQL Server is a hidden gem that is not known to many. If you have a requirement to audit your SQL Server environment and want to keep track of activities such as DBCC commands, backups, restores, failed logins, login creations, database access, database permission changes, user password changes, and trace changes, SQL Server provides a robust auditing feature that can help you achieve this.

In this blog post, we will discuss a simple task of changing the location of the audit log after it has been created. This can be useful in scenarios where you want to store the audit information on a network share, but the network share becomes unavailable due to network issues. We will explore how to seamlessly switch or change the network location using T-SQL.

Let’s go through the steps involved in changing the audit log location:

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

Here is an example of the T-SQL scripts for the above steps:

-- Step 1 - Create an Audit on a network share
CREATE SERVER AUDIT [NetworkAudit] TO FILE (FILEPATH = '\\SQLAuthority\Audit\Network')
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);

-- 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;

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

-- 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);

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

After following these steps, you will be able to change the audit log location from a network share to a local drive seamlessly. However, it is important to note that if you do not have sufficient permissions to write to a local folder on the server, you may encounter errors. In such cases, you will need to adjust the permissions accordingly.

Once you have successfully changed the audit log location, you can access the audit logs using the sys.dm_server_audit_status and sys.fn_get_audit_file system views.

Remember to clean up the objects created in this blog post after you are done:

-- 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];

This concludes the logical steps required to set up and change the audit log location inside SQL Server. The auditing capability in SQL Server provides a powerful tool for tracking and monitoring activities in your database environment.

Thank you for reading!