How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL?

Question

I'm setting up an Extended Events trace in a Managed Instance (NOT Azure SQL DB) for the first time.

These are the steps I've carefully followed:

1. Created a Master key on my Database.
2. Created a Database Scope Credential by using the correct Shared Access Signature.
3. Created and started a trace. This works and everything gets traced as expected.

Now, I'm trying to query the XEL file by using T-SQL:

SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

However, I get two different behaviors:

• From SSMS: I just get zero rows and no error or messages displayed.

• From Azure DataStudio: I get the following error message

Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Some facts:

• I'm able to download the XEL file by using Azure Storage Explorer.
• I have already checked the full path to the XEL file and it's correct. I can download the files directly by using a browser.
• The access level of the container is Public.
• I've tried with different files (running and stopped traces, all of them with data).
• The XEL files are full of data since I query them locally and I can open them by using SSMS.
• I really don't think there's a missing key or scoped credential. Otherwise, the tracing would not work. Anyway, I dropped everything and started again from scratch and the behavior did not change.
• All my tests were carried out by using the managed instance administrator account.

So...

• Is reading XEL files really supported on Managed Instances?
• Is there any cath on how to access the files by using the sys.fn_xe_file_target_read_file function?

Answer 1

I don't know if this is the right solution, but here's is the workaround that worked for me, in case someone is facing the same issue.

1. I created a small Azure SQL Database (S1).
2. I created a master key for this database.
3. I created a scoped credential pointing to the Azub Blob where the XEL files from the Managed Instance reside in (using the same Shared Signature)

I was able to query by using

SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

It seems like Managed Instance does not support the use of this function (or I wasn't able to find the way to do it).

Hope this helps someone.