0

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.