Synapse external table "Unauthorized"

Question

I have created an external table in Azure Synapse from a parquet file stored in an ADLS Gen2 container. I have used the following queries to create the datasource, the file format and the table:

GRANT REFERENCES ON CREDENTIAL::[https://XXXXX.dfs.core.windows.net/XXXXXXX] TO demoiics  
  
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat')   
	CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]   
	WITH ( FORMAT_TYPE = PARQUET)  
GO  
  
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'XXXXX_XXXX_dfs_core_windows_net')   
	CREATE EXTERNAL DATA SOURCE [XXXXX_XXXXX_dfs_core_windows_net]   
	WITH (  
		LOCATION = 'abfss://******@XXXXXX.dfs.core.windows.net',  
		CREDENTIAL = [https://XXXXX.dfs.core.windows.net/XXXXXXX]  
	)  
GO  
  
CREATE EXTERNAL TABLE XXXXXX.e_dim_channel3 (  
	[infa_operation_time] nvarchar(4000),  
	[channel_key] bigint,  
	[channel_name] nvarchar(4000)  
	)  
	WITH (  
	LOCATION = 'contososales_f_dim_channel/**',  
	DATA_SOURCE = [XXXXX_XXXX_dfs_core_windows_net],  
	FILE_FORMAT = [SynapseParquetFormat]  
	)  
GO  

The file is in a Container that allows anonymous access:

Anyway, I have added the database user as a "Storage blob data contributor" (And "data reader", just in case) in the storage account:

The problem is that it is not possible to query the data of the external tables from external clients, such as "SQL Server Management Studio", we always receive the same error:

External table 'XXXXXX.e_dim_channel3' is not accessible because content of directory cannot be listed.  

Any idea?

Thanks.

Answer 1

Hi @Aymon ,

Thank you for posting query in Micrsoft Q&A Platform.

While we try to execute query in external table behind the scenes database scoped credential object which we created in data source will get use to access data from external storage. So, its important to have credential object created with proper credentials.

Also, User must have SELECT permission on an external table to read the data. External tables access underlying Azure storage using the database scoped credential defined in data source using the following rules:

• Data source without credential enables external tables to access publicly available files on Azure storage.
• Data source can have a credential that enables external tables to access only the files on Azure storage using SAS token or workspace Managed Identity - For examples, see the Develop storage files storage access control article.

So could you please make sure you have created credential object with proper credentials and also user has select permission on external table?

Kindly consider checking below videos to understand more about data sources, credential objects, file formats and external tables.

• Create External Data source in Azure Synapse Analytics
• Create External File Format in Azure Synapse Analytics
• External Tables with Synapse SQL in Azure Synapse Analytics

Hope this helps. Please let us know if any further queries. Thank you.

---------
Please consider hitting Accept Answer button. Accepted answers help community as well.