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

azure-sql-managed-instanceextended-eventssql server

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.

enter image description here

  • From Azure DataStudio: I get the following error message

enter image description here

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?

Best Answer

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.

Related Question