2

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?

2 Answers 2

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.

1

This may have not been possible at the time that your question was asked, but it currently supported by Microsoft. I will reproduce their example code:

IF NOT EXISTS (
    SELECT 1
    FROM sys.symmetric_keys
    WHERE name = '##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'REDACTED';

/*
(Re-)create a database scoped credential.
The name of the credential must match the URI of the blob container.
*/
IF EXISTS (
    SELECT *
    FROM sys.credentials
    WHERE name = 'https://exampleaccount4xe.blob.core.windows.net/extended-events-container'
)
DROP CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container];

/*
The secret is the SAS token for the container. The Read and List permissions are set.
*/
CREATE CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
        SECRET = 'sp=rl&st=2023-10-09T22:12:54Z&se=2023-10-10T06:12:54Z&spr=https&sv=2022-11-02&sr=c&sig=REDACTED';

/*
Return event session data
*/
SELECT f.*
--,CAST(f.event_data AS XML)  AS [Event-Data-Cast-To-XML]  -- Optional
FROM sys.fn_xe_file_target_read_file('https://exampleaccount4xe.blob.core.windows.net/extended-events-container/event-session-1', DEFAULT, DEFAULT, DEFAULT) AS f;

I found that it wasn't necessary to create the master key. I'm also not sure why their SAS token secret only has read and list. You need to create a credential with read, list, and write in order to generate the xel files in the first place. Finally, note this tip from the documentation on how sys.fn_xe_file_target_read_file should be called differently than usual:

If you specify a blob name prefix instead of the full blob name in the first argument of sys.fn_xe_file_target_read_file(), the function will return data from all blobs in the container that match the prefix. This lets you retrieve data from all rollover files of a given event session without using the * wildcard, which isn't supported by Azure Storage.

The previous Azure SQL examples omit the xel extension to read all rollover files for a session named event-session-1.

Your Answer

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

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