Reading Azure SQL audit log from Azure Storage using sys.fn_get_audit_file returns empty result

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

This blog post was created on March 6th, 2024.

When you read an Azure SQL Database audit log from Azure Storage using sys.fn_get_audit_file, you might encounter a situation where the audit log appears non-empty, but the query still returns an empty result. This discrepancy can be puzzling, especially when the official documentation doesn’t explicitly mention any limitations or requirements for the sys.fn_get_audit_file system function.

• Use Auditing to analyze audit logs and reports - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn
• sys.fn_get_audit_file (Transact-SQL) - SQL Server | Microsoft Learn

In this post, I will shed light on these limitations and demonstrate them to provide clarity.

Limitations/Requirements

To ensure that sys.fn_get_audit_file functions correctly, consider the following conditions related to audit log files stored in Azure Storage:

1. Container Name: Audit log files must reside in a container named sqldbauditlogs on Azure Storage.

2. File Path: The file path should include the Azure SQL server name. This ensures proper access checking when using sys.fn_get_audit_file. If the server name in the file path doesn’t match the server where you run the function, unexpected empty results may occur.

Failing to meet these requirements won’t trigger an error message; instead, it will lead to unexpected behavior. As a user, this can be confusing during investigations.

When you enable SQL Audit on an Azure SQL Server, the platform automatically places audit log files in a location with a valid file path. Therefore, you’re unlikely to encounter this issue unless you move audit log files around or attempt to read them from a different location.

Demonstrating File Paths

Let’s explore some examples to illustrate what constitutes a good file path and what might lead to issues. Understanding these nuances will help you navigate audit log challenges effectively.

Let's say you have a non-empty audit log file that is put in the default location and the full file path is:

• https://abc.blob.core.windows.net/sqldbauditlogs/server1025/TestDB/SqlDbAuditing_ServerAudit_NoRetention/2023-11-08/02_52_30_128_1.xel

When I ran sys.fn_get_audit_file on the server1025, the correct and expect result is:

If I move the file around and read them from a different location (file path) on server1025, the following 2 file paths will show the same result because the file paths are valid (They have the matched server name in the file path and the container name is ‘sqldbauditlogs’)

• https://abc.blob.core.windows.net/sqldbauditlogs/server1025/TestDB/02_52_30_128_1.xel
• https://abc.blob.core.windows.net/sqldbauditlogs/server1025/02_52_30_128_1.xel

And these four file paths will not work (returning empty result:(

• https://abc.blob.core.windows.net/testcontainer/sqldbauditlogs/server1025/TestDB/SqlDbAuditing_ServerAudit_NoRetention/2023-11-08/02_52_30_128_1.xel
  • Contianer name is incorrect
• https://abc.blob.core.windows.net/sqldbauditlogs/02_52_30_128_1.xel
  • No server name
• https://abc.blob.core.windows.net/sqldbauditlogs/testfolder/server1025/02_52_30_128_1.xel
  • The hierarchy of server name is incorrect
• https://abc.blob.core.windows.net/sqldbauditlogs/wrongserver1234/02_52_30_128_1.xel
  • the server name does not match server1025 where the query is run

The result will be like:

Conclusion

By adhering to these guidelines, you’ll avoid unexpected results and streamline your audit log analysis.

(End of this post)