How To Read/Write Files from/to Blob Storage with Storage Firewall enabled for Managed Instance

Microsoft

Background

Previously, we have discussed about how to read files from blob storage with storage firewall enabled for Azure SQL Database. Please refer to the below online blog for more information.

https://techcommunity.microsoft.com/t5/azure-sql-database/how-to-read-files-from-blob-storage-with-s...

 

When configured firewall rules of storage accounts for Managed Instance, audit logs could no longer be recorded into atorage account, and user will received such email notification as below.

"Audit logs for database 'xxxxxxx' on server 'xxxxxxx' are not being recorded in storage account 'xxxxxxx'"

 

There is the option of 'Allow trusted Microsoft service to access this storage account' under storage account firewall configuration page, unfortunately, according to our online document, Managed Instance is not yet considered as trusted service. 

Refers to https://docs.microsoft.com/en-nz/azure/storage/common/storage-network-security#exceptions

 

Moreover, comparing with SQL Azure, Managed Instance does not support Managed Identity with the error below in the errorlog.

1.png

 

Guidance

To enable the read/write access to Azure Storage with Firewall turned on, users need to add Managed Instance’s subnet to Storage Account Vnet firewall rules with leveraging the MI subnet delegation and Storage service endpoint.

 

Firstly, users would need to determine  which subnet the managed instance has been deployed on.

2.png

After selecting the specific subnet, change the configuration of Subnet delegation to Managed Instances.

3.png

Following, users can delegate this subnet to Managed Instance, please wait for approximately one hour, and arrange Storage as a service added to service endpoints.

4.png

 

After both of the above steps have been performed successfully, add the configured Vnet/ Subnet to Virtual networks rules of storage account.

5.png

 

Using this workaround, users are able to write audit logs to storage account with firewall rules configured.

 

Please note, the above steps could only be performed when the storage account and the managed instance are in the same or paired region.

In this case, the Managed Instance was deployed in East Asia, and the storage account is deployed in East US. While adding the configured Vnet/ Subnet to Virtual network rules of the storage account, user would not see any available virtual networks from the list and also portal will show a notification that only virtual networks in 'East US' and 'West US' will be listed, which is paired region.

6.png

Terminology Behind the Guidance

The workaround delegates the specific subnet to Managed Instance, and enables storage service endpoints on this subnet, thus, the subnet will be able to access the storage account through service endpoint. 

 

According to our online document, "Subnet delegation enables you to designate a specific subnet for an Azure PaaS service of your choice that needs to be injected into your virtual network. Subnet delegation provides full control to the customer on managing the integration of Azure services into their virtual networks."

Refers to https://docs.microsoft.com/en-us/azure/virtual-network/subnet-delegation-overview

7.png

 

Using the same terminology, can we access storage account to read files?

Before adding the configured Vnet/subnet to storage account, users will have denied access to storage account to read files from a storage account which has configured firewall rules.

 

BULK INSERT CSVtest

FROM 'product.csv'

WITH ( DATA_SOURCE = 'CSVInsert',

Format='CSV'

);

 

        Msg 4861, Level 16, State 1, Line 40

Cannot bulk load because the file "product.csv" could not be opened. Operating system error code 5(Access is denied.).

 

After performing the above workaround, users should be able to read files and access the storage account as the subnet of the Managed Instance has been whitelisted.

 

9.png

 

Author: Marlon Jin <marlonj@microsoft.com>; Yvonne Zhou <wenzho@microsoft.com>

Please feel free to contact us if any questions. 

0 Replies