Azure SQL Database sys.fn_get_audit_file or sys.fn_xe_file_target_read_file troubles

Question

I'm having troubles on a Azure SQL Database where i'm trying to read DB Audit logs.

Both procedures sys.fn_get_audit_file or sys.fn_xe_file_target_read_file sould be able to read a file.

But whatever I do i'm getting blank tables.But, even if I specify a non existing file I receive a table with zero records instead of a error.

So I'm afraid its something else.

My login is in the db_owner group.

Any suggestions ?

Answer 1

I found that I could only read XEL files by using the same server and same database context that they were created for. So for example, consider the following scenario:

• ServerA is the Azure Synapse instance I was creating the audit XEL files from, all related to DatabaseA
• ServerB is a normal SQL instance that I want to read the XEL files on

Test 1: Using ServerB, try to read file directly from blob storage

Result: 0 rows returned, no error message

Test 2: Using ServerB, download the XEL files locally, and try to read from the local copy

Result: 0 rows returned, no error message

Test 3: Using ServerA, with the current DB = 'master', try to read file directly from blob storage

Result: 0 rows returned, no error message

Test 4: Using ServerA, with the current DB = 'DatabaseA', try to read file directly from blob storage

Result: works perfectly

Because I really wanted to read the files from ServerB, I also tried doing a CREATE CREDENTIAL there that was able to read & write to my blob storage account. That didn't make any difference unfortunately - a repeat of Test 1 got the same result as before.