Storage configuration for external table is not accessible while query on Serverless

Published 12-29-2020 06:34 AM 2,837 Views
Microsoft

 

Following this lab: Lab: Serverless Synapse - From Spark to SQL On Demand - Microsoft Tech Community

You may experience this message: 

Failed to execute the query because content of directory cannot be listed) 

This is due to an extra step required to enable the AAD to pass through the firewall on the storage.

 

Scenario:

Query SQL Serverless failed as following while querying Spark. 

severless_spark.png

 

On Azure Portal, Storage Account -> Networking is configured as follows ( with Allow trusted Microsoft services to access this account enabled):

firewall_storage_conf.png

If you change to All Networks it does work successfully. 

 

Mitigation: Enable the AAD to pass through the firewall on the storage.

 

Microsoft DocControl storage account access for serverless SQL pool - Azure Synapse Analytics | Microsoft Docs

 

 

Run the following-> exactly like this example but replace the values as required: 

 

 

Add-AzStorageAccountNetworkRule -ResourceGroupName $resourceGroupName -Name $accountName -TenantId $tenantId -ResourceId "/subscriptions/<subscriptionid>/resourcegroups/<rgname>/providers/Microsoft.Synapse/workspaces/<workspacename>"

 

 

Requisites:

 

1) For the Tenant ID you can get the value from the Azure Portal ->AAD

tenant_id.png

 

 

2) Note you may need to install Power Shell version 3 like my example:

(PowerShell Gallery | Az.Storage 3.0.1-preview)

powershell3.png

 

Thanks to Stefan Azaric.

 

That is it!

Liliam

UK Engineer.

1 Comment
New Contributor

Another option is to set up the data source for the external table to use the Synapse Managed Identity. This way you can then secure the external table using GRANT statements on the external table and have Synapse request the data from the Data Lake. 

%3CLINGO-SUB%20id%3D%22lingo-sub-2020523%22%20slang%3D%22en-US%22%3EStorage%20configuration%20for%20external%20table%20is%20not%20accessible%20while%20query%20on%20Serverless%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2020523%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20this%20lab%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-synapse-analytics%2Flab-serverless-synapse-from-spark-to-sql-on-demand%2Fba-p%2F1643510%22%20target%3D%22_blank%22%3ELab%3A%20Serverless%20Synapse%20-%20From%20Spark%20to%20SQL%20On%20Demand%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20experience%20this%20message%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EFailed%20to%20execute%20the%20query%20because%20content%20of%20directory%20cannot%20be%20listed)%3C%2FEM%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20due%20to%20an%20extra%20step%20required%20to%20enable%20the%20AAD%20to%20pass%20through%20the%20firewall%20on%20the%20storage.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScenario%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EQuery%20SQL%20Serverless%20failed%20as%20following%20while%20querying%20Spark.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22severless_spark.png%22%20style%3D%22width%3A%20696px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243347iEC6A1672061B13FF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22severless_spark.png%22%20alt%3D%22severless_spark.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20Azure%20Portal%2C%20Storage%20Account%20-%26gt%3B%20Networking%20is%20configured%20as%20follows%20(%20with%20%3CSPAN%3E%3CSTRONG%3EAllow%20trusted%20Microsoft%20services%20to%20access%20this%20account%3C%2FSTRONG%3E%20enabled)%3C%2FSPAN%3E%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22firewall_storage_conf.png%22%20style%3D%22width%3A%20451px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243344i5366BA566423B78B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22firewall_storage_conf.png%22%20alt%3D%22firewall_storage_conf.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20change%20to%20%3CEM%3EAll%20Networks%3C%2FEM%3E%20it%20does%20work%20successfully.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EMitigation%3C%2FSTRONG%3E%3A%20Enable%20the%20AAD%20to%20pass%20through%20the%20firewall%20on%20the%20storage.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CU%3E%3CSTRONG%3EMicrosoft%20Doc%3C%2FSTRONG%3E%3C%2FU%3E%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fdevelop-storage-files-storage-access-control%3Ftabs%3Duser-identity%23configuration-via-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EControl%20storage%20account%20access%20for%20serverless%20SQL%20pool%20-%20Azure%20Synapse%20Analytics%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun%20the%20following-%26gt%3B%20exactly%20like%20this%20example%20but%20replace%20the%20values%20as%20required%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EAdd-AzStorageAccountNetworkRule%20-ResourceGroupName%20%24resourceGroupName%20-Name%20%24accountName%20-TenantId%20%24tenantId%20-ResourceId%20%22%2Fsubscriptions%2F%3CSUBSCRIPTIONID%3E%2Fresourcegroups%2F%3CRGNAME%3E%2Fproviders%2FMicrosoft.Synapse%2Fworkspaces%2F%3CWORKSPACENAME%3E%22%3C%2FWORKSPACENAME%3E%3C%2FRGNAME%3E%3C%2FSUBSCRIPTIONID%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3ERequisites%3C%2FU%3E%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20For%20the%20Tenant%20ID%20you%20can%20get%20the%20value%20from%20the%20Azure%20Portal%20-%26gt%3BAAD%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tenant_id.png%22%20style%3D%22width%3A%20569px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243349i95274F1FEDF9FB47%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22tenant_id.png%22%20alt%3D%22tenant_id.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Note%20you%20may%20need%20to%20install%20Power%20Shell%20version%203%20like%20my%20example%3A%3C%2FP%3E%0A%3CP%3E(%3CA%20href%3D%22https%3A%2F%2Fwww.powershellgallery.com%2Fpackages%2FAz.Storage%2F3.0.1-preview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EPowerShell%20Gallery%20%7C%20Az.Storage%203.0.1-preview%3C%2FA%3E)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22powershell3.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243346iE6C82A9F787E6226%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22powershell3.png%22%20alt%3D%22powershell3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20to%20Stefan%20Azaric.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20it!%3C%2FP%3E%0A%3CP%3ELiliam%3C%2FP%3E%0A%3CP%3EUK%20Engineer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2020523%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20query%20a%20Spark%20table%20you%20may%20experience%20this%20message%3A%3C%2FP%3E%0A%3CP%3E%3CEM%3EFailed%20to%20execute%20the%20query.%20Error%3A%20External%20table%20'default'%20is%20not%20accessible%20because%20content%20of%20directory%20cannot%20be%20%3CSTRONG%3Elisted.%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2020523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Administration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Spark%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113668%22%20slang%3D%22en-US%22%3ERe%3A%20Storage%20configuration%20for%20external%20table%20is%20not%20accessible%20while%20query%20on%20Serverless%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113668%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20option%20is%20to%20set%20up%20the%20data%20source%20for%20the%20external%20table%20to%20use%20the%20Synapse%20Managed%20Identity.%20This%20way%20you%20can%20then%20secure%20the%20external%20table%20using%20GRANT%20statements%20on%20the%20external%20table%20and%20have%20Synapse%20request%20the%20data%20from%20the%20Data%20Lake.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Jan 08 2021 02:48 AM
Updated by: