-1

I need to investigate some events from Azure SQL Database.

The Audit logs were stored on a Storage Account and I have downloaded them on my laptop. They are now all in the same folder.

There are many guides that explains you how to import such files into SQL Server:

But when I try to run the query

SELECT event_data = convert(XML, event_data)
FROM sys.fn_xe_file_target_read_file(N'C:\Users\FrancescoMantovani\Downloads\Investigate\*.xel', NULL, NULL, NULL);

I receive the error:

Msg 25718, Level 16, State 3, Line 1 The log file name "C:\Users\FrancescoMantovani\Downloads\Investigate*.xel" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

What am I doing wrong?

EDIT: I tried to put the files into the DATA folder inside Microsoft SQL Server, the error has now changed. Command:

SELECT event_data = convert(XML, event_data)
FROM sys.fn_xe_file_target_read_file(N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Investigate\00_00_06_511_24.xel', NULL, NULL, NULL);

Error:

Msg 25748, Level 16, State 9, Line 1
The file "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Investigate\00_00_06_511_24.xel" contains audit logs. Audit logs can only be accessed by using the fn_get_audit_file function.
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • 1
    The error message seems pretty self-explanatory. Does the account under which SQL Server executes have access to the `C:\Users\FrancescoMantovani\Downloads` folder? If not, can you put the files somewhere that it does, like maybe `C:\Temp`? – AlwaysLearning Aug 03 '23 at 09:59
  • The xel file is not XML (or bad XML). XML is text so open the file with a text editor and verify that file is XML. There are online XML checkers that you can use. – jdweng Aug 03 '23 at 12:08
  • Franceso, try creating a new folder C:\xelfiles. Try adding Windows permissions to "everyone" as "full control". Once you have imported the files you can remove the folder. – Alberto Morillo Aug 03 '23 at 12:31
  • Nice catch. I have updated my question. The error has now changed. Thank you all for your help – Francesco Mantovani Aug 03 '23 at 15:07
  • 1
    RE: Edit. Seriously? Just read the error messages. Both of the ones you have posted have told you the problem – Martin Smith Aug 03 '23 at 15:58
  • use [sys.fn_get_audit_file](https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-ver16) to read audit file in sql. – Pratik Lad Aug 04 '23 at 12:33
  • Thank you @PratikLad but it's not working. – Francesco Mantovani Aug 07 '23 at 08:30

1 Answers1

0

Because the audit files were from Azure SQL Database it was easier for me to use a direct connection to the Storage Account this way:

SELECT *
FROM sys.fn_get_audit_file('https://mystorageaccount.blob.core.windows.net/sqldbauditlogs/weu-cust-npr-mssql-srvr/mydatabase/SqlDbAuditing_ServerAudit/2023-07-29/00_00_06_604_12.xel', DEFAULT, DEFAULT);
GO

And if you want to query the whole folder:

SELECT *
FROM sys.fn_get_audit_file('https://mystorageaccount.blob.core.windows.net/sqldbauditlogs/weu-cust-npr-mssql-srvr/mydatabase/SqlDbAuditing_ServerAudit/2023-07-29/', DEFAULT, DEFAULT);
GO
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113