3

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 ?

Harry Leboeuf
  • 723
  • 12
  • 30
  • Have you enabled auditing fro your Azure SQL database : https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview? – Jim Xu Jun 08 '20 at 03:50
  • Yes I did, I have the files on the Blob, but from the server I can't read them with these functions. – Harry Leboeuf Jun 08 '20 at 07:22
  • What is strange to me is that this function needs no extra authentication to be able to read the blob. – Harry Leboeuf Jun 08 '20 at 07:23
  • Could you please check if you can read audit records via Azure Porta( -> Auditing -> View audit logs): https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview#subheading-3 – Jim Xu Jun 08 '20 at 07:50
  • That was the first thing I did, yes there are records. I see the file increasing is size too. – Harry Leboeuf Jun 08 '20 at 11:06

1 Answers1

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.

Mike
  • 1,686
  • 19
  • 14