Not able to get Azure SQL Server Extended Events to work when Blob Storage is set to Enabled from selected virtual networks and IP addresses

Question

So I have an Azure Database and want to test extended events with the database.

I was able to set up my Blob Storage container and was able to get Extended Events via Azure Database to work as long as the Blob Storage network setting Public network access is set to Enabled from all networks. If I set Enabled from selected virtual networks and IP addresses and have Microsoft network routing checked as well as Resource type set with Microsoft.Sql/servers and its value as All In current subscription, it still doesn't work.

I'm not exactly sure what I'm doing wrong and I'm not able to find any documentation on how to make it work without opening up to all networks.

The error I'm getting is:

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) (Microsoft SQL Server, Error: 25602)

Edit - Steps to fix the issue

@Imran: Your answer led me to get everything working. The information you gave and the link provided was enough for me to figure it out.

However, for anyone in the future I want to give better instructions.

The first step I had to do was:

All I had to do was run Set-AzSqlServer -ResourceGroupName [ResourcegroupName] b -ServerName [AzureSQLServerName] -AssignIdentity.

This assigns the SQL Server an Azure Active Directory Identity. After running the above command, you can see your new identity in Azure Active Directory under Enterprise applicationsand then where you see theApplication type == Enterprise Applicationsheader, click the headerApplication type == Enterprise Applicationsand change it toManaged Identities`and click apply. You should see your new identity.

The next step is to give your new identity the role of Storage Blob Data Contributor to your container in Blob Storage. You will need to go to your new container and click Access Control (IAM) => Role assignments => click Add => Add Role assignment => Storage Blob Data Contributor => Managed identity => Select member => click your new identity and click select and then Review + assign

The last step is to get SQL Server to use an identity when connecting to `Blob Storage.

You do that by running the command below on your Azure SQL Server database.

CREATE DATABASE SCOPED CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>]
WITH IDENTITY = 'Managed Identity';
GO   

You can see your new credentials when running

SELECT * FROM sys.database_scoped_credentials

The last thing I want to mention is when creating Extended Events with an Azure SQL Server using SSMS, it gives you this link. This only works if you want your Blob Storage wide open. I think this is a disservice and wish they would have instructions when you want your Blob Storage not wide open by using RBAC instead of SAS.

Answer 1

I tried to reproduce the same in my environment I got the result successfully like below:

To resolve this issue, check whether your account type should be StorageV2(general purpose v2). If you have a general-purpose v1 or blob storage account, try to upgrade like below.

In storage account -> under setting, configuration -> upgrade

Check whether you have choose Allow trusted Microsoft services to access this storage account under exception and I added firewall client Ip address range and vnet like below.

• Make sure Microsoft.Authorization/roleAssignments/write permission in your storage account

After enabling firewall, we lose write access to the storage account and audit logs try to Resave the audit settings from the portal is required in order for auditing to function like below.

Note: Auditing to storage behind firewalls using user managed identity authentication type is not presently supported.

When I try to connect, I got result successfully like below:

Reference:

Configure extended events in SQL Azure to the blob storage with Private Endpoint - Microsoft Community Hub by Sakshi Gupta