I have a storage account with firewall settings enabled (restricted access from only certain IPs and VNets). I need to access this storage account from Az Dedicated SQL Pool residing inside a SQL server. For this, I am using the external table concept. All works well when the storage account firewall is disabled. However, when I enable the firewall, I am not able to access data as the SQL Server does not belong to any VNet that can be whitelisted at the storage account. I do not want to remove the firewall of the storage account for security reasons. How can data access be ensured in this case?

PS: I also tried with the resource instance rules at the SA. This will not work in my case, as the SQL Server and Storage Account reside in different tenants.

Msg 105019, Level 16, State 1, Line 1
External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:
HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "This request is not authorized to perform this operation.", 403, HEAD, https://xxx.dfs.core.windows.net/xxx/xxx.csv?upn=false&action=getStatus&timeout=90'

This is the error when the firewall is enabled. Without the firewall, I am able to see the data.

CodePudding user response：

• I would suggest you to please ensure that you check/enable the below option in the snapshot while configuring specific resource instances for having access to the storage account: -

Allow Azure services on the trusted services list to access this storage account.

Also, select the specific resource instance that will have access to your storage account, i.e., your configured ‘Azure SQL Server’ on which your database is hosted and want to connect to. Since, communication between an Azure SQL Server and a storage account traverses through the Azure backbone network, which is secure, encrypted on both the sides and is deserted from the rest of the public internet, it is included in the list of trusted sources for connectivity purposes.

• Thus, would suggest you configure the same but also configure the access to this storage account through a system assigned managed identity for the Azure SQL Database server as only then the access based on it would be granted access as Azure considers it safe and by design, it is necessary.

Kindly refer the below link for more detailed clarification on this: -

#create master key
CREATE MASTER KEY [ENCRYPTION BY PASSWORD = 'somepassword'];

#create scoped credentials using managed identity created un first step
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

#create External Data Source for connection
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, 
LOCATION = 'abfss://[email protected]', 
CREDENTIAL = msi_cred);