Question 105162 in Azure Question posted in
The official documentation to get you started can be found here.

Connect to storage account with firewalls enabled from SQL Server in Azure

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.

Tags:

2

Answers


  1. 0

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

    Storage account firewall

    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: –

    https://learn.microsoft.com/en-us/azure/storage/common/storage-network-security?tabs=azure-portal#trusted-access-based-on-a-managed-identity

    To configure a system managed assigned identity to an Azure SQL DB Server, kindly follow the below link: –

    https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql#set-managed-identities-in-the-azure-portal

    Login or Signup to reply.
  2. 0

    To connect storage account with firewalls enabled from SQL Server in Azure

    We must use the ABFSS driver and Managed Service Identity Credential that connects to a Secured Storage account with Polybase.

    1. As you have standalone dedicated SQL pool (formerly SQL DW) create the Managed Service Identity for it.
    Connect-AzAccount  
Select-AzSubscription -SubscriptionId your-subscriptionId  
Set-AzSqlServer -ResourceGroupName your-server-resourceGroup -ServerName your-servername -AssignIdentity

    enter image description here

    1. You must first upgrade to general-purpose v2 if you currently have a general-purpose v1 or Azure Blob Storage account.

    Under Access Control (IAM) on your storage account. Give your SQL Database server the RBAC role of Storage Blob Data Contributor.
    enter image description here

    1. To connect with secured Azure Storage account.
    #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);

    Reference:Use virtual network service endpoints and rules for servers in Azure

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search