0

When I try to query our Serverless SQL pool in Azure Synapse Analytics I get the following error:

"Content of directory on path 'https://xxxxxx.dfs.core.windows.net/dataverse-xxxxxx-org5a2bcccf/account/Snapshot/2018-08_1656570292/*.csv' cannot be listed.".

I have checked out the following link for clues as to what could be cause:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002

It is suggested that the error is due permissions:

However, I believe I have the correct permissons,

enter image description here

I get this error whether I try to execute the query in SSMS or Synapse Workspace.

The error in SSMS is as follows:

Warning: Unable to resolve path https://xxxxx.dfs.core.windows.net/dataverse-xxxxx-org5a2bcccf/account/Snapshot/2018-10_1657304551/*.csv. Error number 13807, Level 16, State 1, Message "Content of directory on path 'https://xxxxxx.dfs.core.windows.net/dataverse-xxxxx-org5a2bcccf/account/Snapshot/2018-10_1657304551/*.csv' cannot be listed.".

enter image description here

Can someone let me know how to resolve this? The query that I'm attempting to execute can be located here: https://github.com/slavatrofimov/Synapse-Link-for-Dataverse-data-enrichment-in-Serverless-SQL-Pools/blob/main/SQL/Enrich%20Synapse%20Link%20for%20Dataverse%20Entities%20with%20Human-Readable%20Labels.sql

Is there a definitive way to determine if the problem is due to lack of permissions?

Update Question: I have just realised that the issue is access the Lake on https://xxxxxx.dfs.core.windows.net/dataverse-xxxxxx-org5a2bcccf/

Therefore please take a look at my permissons on the lake and let me know if it is sufficient? enter image description here

Patterson
  • 1,927
  • 1
  • 19
  • 56

2 Answers2

1

This issue occurs when the user trying to query the external table does not have the relevant permissions or if there is a firewall enabled on your storage network.

When looked at the permissions you have provided, I see Storage Blob Data reader and Storage Blob Data contributor have been given.

Ref doc: Control storage account access for serverless SQL pool in Azure Synapse Analytics

enter image description here

In case if your storage account is firewall protect then you will have to follow the steps described in this document to overcome the issue: Access storage that is protected with the firewall

Here are couple of relevant articles which might help you configure your storage firewall to overcome this issue:

  1. Storage configuration for external table is not accessible while query on Serverless
  2. Synapse Studio error while trying to read data from Storage Account using SQL On Demand
Kranthi Pakala
  • 1,288
  • 5
  • 10
0

The solution may not be complex. Check the column data type using below:

exec sp_describe_first_result_set N'SELECT 
    TOP 10 *
FROM
    OPENROWSET(
        BULK ''data source url'',
        FORMAT = ''DELTA''
    ) AS [result]'

If your data type is not proper and big digressively like varchar(8000), you encounter this error. Just write necessary columns and partition columns of file(e.g. year, month).

SELECT TOP 100
       *
   FROM OPENROWSET(
       BULK 'data source url',
       FORMAT = 'DELTA'
   ) 
   WITH (
        column1 datatype1,
        column2 datatype2,
        ...
        year VARCHAR(4),   --if year is a partition column of file, it is required.
        month VARCHAR(2)   --if month is a partition column of file, it is required.
   )AS [result];
EbruB.
  • 76
  • 4