Brining data from Serverless pool to power bi

[pinakibasu]

Hi

I have a table created in a serverless pool. I have given the data contributor and data reader roles to the storage account.

What I did was create an external table from multiple parquet files.

I want to read the same table in the power bi.

I can see the tables in the power bi, but when I click on the table - get the following error -

DataSource.Error: Microsoft SQL: External table 'city_temp' is not accessible because content of directory cannot be listed.

Could not use view or function 'dbo.v_city_temp' because of binding errors.

Details:

DataSourceKind=SQL

DataSourcePath=sandbxws-ondemand.sql.azuresynapse.net;serverlessdb

Message=External table 'city_temp' is not accessible because content of directory cannot be listed.

Could not use view or function 'dbo.v_city_temp' because of binding errors.

ErrorCode=-2146232060

Number=16561

Class=16

State=1

I do not know what I am doing wrong.
My log in to azure is the same as the Power bi

[Ritaf1983]

Hi @pinakibasu 

This error usually indicates that Power BI cannot access the underlying storage due to permissions or configuration issues. Here are some steps to troubleshoot:

1. **Storage Permissions**:
- Although you mentioned assigning **Data Contributor** and **Data Reader** roles to the storage account, make sure these roles are assigned at the **container level** for the data lake, specifically on the directory where your Parquet files are stored.
- Verify that the service principal or account being used in Power BI has read access to the storage account.

2. **Synapse Serverless SQL Permissions**:
- In addition to storage permissions, ensure that the SQL user or service principal has the correct permissions on the Synapse SQL pool itself, specifically **SELECT** permissions on the external table.

3. **Synapse External Table Configuration**:
- Check that the external table in Synapse is set up correctly. Synapse serverless SQL pools rely on **PolyBase** to access external storage, and the directory path in the external table definition must match exactly with the storage structure.
- Confirm that the **data source** and **file format** used for creating the external table are configured correctly.

4. **Firewall Settings**:
- Ensure that the Synapse workspace and storage account have the appropriate network configurations to allow access. If there are firewall rules in place on the storage account, make sure that the Synapse managed identity or IP address has access.

5. **Using the Same Account in Power BI**:
- Sometimes, even if you are logged in with the same account in Azure and Power BI, cached credentials or other authentication issues can arise. Go to **Data source settings** in Power BI, clear any cached credentials for your Synapse server, and then re-authenticate.

6. **Testing Access in Synapse**:
- As a diagnostic step, try running a query on the external table (`SELECT * FROM city_temp`) directly in Synapse Studio to see if it’s accessible there. If you get a similar error, it points to an issue with the table configuration or permissions.

If none of these steps resolve the issue, consider re-creating the external table, ensuring that all paths and permissions are correct. 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly