Blog Post

Azure Database Support Blog
2 MIN READ

Configure extended events in SQL Azure to the blob storage with Private Endpoint

sakshigupta's avatar
sakshigupta
Icon for Microsoft rankMicrosoft
Feb 16, 2022

How to enable extended events in SQL Azure to blob storage running behind the VNET or Private Endpoints?

 

Recently I have come across an issue for enabling extended event to blob storage on Azure SQL Server and getting the below error.

 

The target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", encountered a configuration error during initialization. Object cannot be added to the event session. (null) (.Net SqlClient Data Provider)

 

 

 

Investigation/Resolution

 

Storage account was running behind the VNET.

 

Below are the steps required for creating extended event for the storage account is running behind the VNET.

 

  • Set identity for Azure SQL Server

 

Set-AzSqlServer -ResourceGroupName [ResourcegroupName] b -ServerName [AzureSQLServerName] -AssignIdentity

 

  • Provide the below permission to the storage account for respective Azure SQL Identity.

 

 

 

  • Ensure to add the necessary firewall and virtual network so that storage account can communicate with Azure SQL database.

 

  • Create credential in Azure SQL using Managed Identity.

 

CREATE DATABASE SCOPED CREDENTIAL [https://storageaccount.blob.core.windows.net/container] WITH IDENTITY = 'Managed Identity';

 

  • Create extended events and select the credentials created above to store to blob.

 

Sample code:

 

CREATE EVENT SESSION WithPrivateLink ON DATABASE

ADD EVENT sqlserver.error_reported(

    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)

    WHERE (([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))),

ADD EVENT sqlserver.rpc_completed(

    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)

    WHERE (([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))),

ADD EVENT sqlserver.sql_batch_completed(

    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)

    WHERE (([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))))

ADD TARGET package0.event_file(SET filename=N'https://123extendedeventtesting.blob.core.windows.net/extendedevent/FileName.xel'),

ADD TARGET package0.ring_buffer

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

 

  • Start the session and can see the files are getting created inside the blob storage.

 

 

 

 

Happy learning!!

 

Reference Article:

Audit to storage account behind VNet and firewall - Azure SQL Database | Microsoft Docs

 

 

Updated Feb 16, 2022
Version 2.0
  • Kez_Halls's avatar
    Kez_Halls
    Copper Contributor
    May 26, 2023

    Hi there, I see this article is quite old.  wanting to confirm you have this working with Azure SQL database.

    I have not been able to get this to work.

     

    Thanks

  • SujaiSparks's avatar
    SujaiSparks
    Copper Contributor
    Aug 25, 2023

    We can also configure this using SAS key identity. Here is a template that I use for troubleshooting blocks and deadlocks in azure sql database..

     


    IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 'https://myazurestorageaccount.blob.core.windows.net/extended-events-log')
    DROP DATABASE SCOPED CREDENTIAL [https://myazurestorageaccount.blob.core.windows.net/extended-events-log]
    GO

    CREATE DATABASE SCOPED CREDENTIAL [https://myazurestorageaccount.blob.core.windows.net/extended-events-log]
    WITH IDENTITY='SHARED ACCESS SIGNATURE'
    --Remove ? mark from the SAS token.
    --Ensure the expiry datetime of the SAS token covers the duration of the extended events log collection time
    , SECRET = 'sv=2022-11-02&ss=bfqt&srt=sco&sp=rwdlacupiytfx&se=2023-12-30T20:39:42Z&st=2023-08-23T12:39:42Z&spr=https&sig=Abcdef%1ghijkl%2mnopq%3rstuvwxyz%4sujai%3D'
    GO

    IF EXISTS(SELECT 'true' FROM sys.database_event_sessions WHERE name = 'my_XE_Trace')
    DROP EVENT SESSION [my_XE_Trace] ON DATABASE
    Go

    CREATE EVENT SESSION [my_XE_Trace] ON DATABASE
    ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[database_name]=N'my_database_name')),
    ADD EVENT sqlserver.database_xml_deadlock_report(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[database_name]=N'my_database_name')),
    ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name') AND [duration]>=(5000000))),
    ADD EVENT sqlserver.lock_deadlock_chain(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'my_database_name')),
    ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE (((([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))) AND ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name'))) AND ([duration]>(5000000)))),
    ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE (((([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))) AND ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'my_database_name'))) AND ([duration]>(5000000)))),
    ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'my_database_name')),
    ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'my_database_name'))
    ADD TARGET package0.event_file(SET filename=N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817.xel',max_file_size=(10240))
    WITH (STARTUP_STATE=OFF)
    GO

    SELECT * FROM sys.database_event_session_events


    SELECT distinct object_name FROM sys.fn_xe_file_target_read_file
    (N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817_0_133373392162450000.xel',
    null, null, null) ;


    SELECT * FROM sys.fn_xe_file_target_read_file
    (N'https://myazurestorageaccount.blob.core.windows.net/extended-events-log/LoadTest20230817/LoadTest20230817_0_133373392162450000.xel',
    null, null, null)
    Where object_name = 'blocked_process_report';