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