Configure extended events in SQL Azure to the blob storage with Private Endpoint
Published Feb 16 2022 06:57 AM 5,472 Views
Microsoft

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)

 

sakshigupta_0-1645022100456.png

 

 

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.

 

sakshigupta_1-1645022100460.png

 

 

  • 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.

 

sakshigupta_2-1645022100462.png

 

 

sakshigupta_3-1645022100464.png

 

Happy learning!!

 

Reference Article:

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

 

 

2 Comments
Co-Authors
Version history
Last update:
‎Feb 16 2022 07:37 AM
Updated by: