403 Forbidden when accessing Storage Account through firewall from Azure Synapse's dedicated SQL pool

Question

Getting a 403 Forbidden when trying to access a firewall'd Storage Account from a dedicated SQL pool in Azure Synapse.

It works when I disable the Storage Account firewall.

Relevant configuration:

• Vnet: 10.0.0.0/16 with a Snet of 10.0.2.0/24

• Storage account

  • Hierarchical Namespace: enabled
  • Resource instances added: Microsoft.Synapse/workspaces
  • "Allow" Azure services on the trusted services list to access this storage account: enabled
  • Public IP address from (AWS-hosted) added to the firewall allowlist (the one initiating the COPY INTO command)
  • Virtual network: Linked to above Vnet
  • Storage Blob Data Contributor role added for the Synapse Workspace app
  • No specific ACL on the container/file system

• Synapse Workspace

  • Managed Virtual Network: enabled
  • Managed Private Endpoint: added for Blob and Data Lake access to the storage account, approved
  • Linked Service connection test to Blob and DFS: successful

• Dedicated SQL pool

  • Master key created
  • Database scoped credential added
  • External data source added with CREATE EXTERNAL DATA SOURCE [DataSource] WITH (TYPE = HADOOP, LOCATION = 'abfss://${var.datalake_container_name}@${var.datalake_hostname}', CREDENTIAL = [ScopedCredential]);

Error in the StorageBlobLogs:

OperationName=GetBlob
StatusCode=403 
StatusText=AuthorizationFailure 
CallerIpAddress=10.0.0.11:34573 
AuthenticationType=AccountKey

Error in the client app:

'copy into "myschema"."mytable" from 'https://mystorageaccount.blob.core.windows.net/mycontainer/abcde/' with (credential = (identity = 'Storage Account Key', secret = 'xxx'), file_type = 'csv', fieldterminator = ',', rowterminator = '0x0a', firstrow = 2, encoding = 'utf8');

Not able to validate external location because The remote server returned an error: (403) Forbidden.

Any pointers would be appreciated.

Answer 1

The problem was that the COPY INTO command does not support Storage Account Access key.

This works:

copy into "myschema"."mytable" 
from 'https://mystorageaccount.blob.core.windows.net/mycontainer' 
with (credential = (identity = 'Managed Identity'), file_type = 'csv', fieldterminator = ',', rowterminator = '0x0a', firstrow = 2, encoding = 'utf8');

This is supported in this Microsoft docs page:

When accessing storage that is protected with the firewall, you can use User Identity or Managed Identity.

However this docs page mentioned only Serverless SQL pools, not (also) dedicated SQL pools.