Cannot query data from Fabric Lakehouse SQL Analytics Endpoint using Service Principal (only schema)

[JGAlltricks]

Hi everyone,

I’ve been working with Microsoft Fabric and so far I’ve been able to:

• Write parquet files into a Lakehouse (remotely with python/azcopy),

• Define views with SQL remotely using OPENROWSET,

• To do that, I made connection using a Service Principal (App Registration).

Now I’m trying to query one of these views from the SQL Analytics Endpoint of the Lakehouse.
The view is based on parquet files stored under the Files folder of the Lakehouse.

When I execute a simple SELECT * query on that view via Python (using SQLAlchemy and ODBC, following this guide https://medium.com/@mariusz_kujawski/connect-to-microsoft-fabric-warehouse-using-python-and-sqlalche...), I only get back the columns of the view but 0 rows.

It feels like my Service Principal only has access to the schema (metadata) but not the underlying parquet data when executing queries.

If I run the exact same query from the Fabric web interface or Azure Data Studio (using my user account), the query works perfectly and returns the expected rows.

Has anyone run into this before? Do I need to explicitly grant ReadData or some specific permission to the Service Principal on the Lakehouse / SQL Endpoint? Or should I issue a GRANT SELECT at the schema level to make the parquet files readable?

Thanks a lot for any guidance!

[v-dineshya]

Hi @JGAlltricks ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Shahid12523 , Thank you for your prompt response.

Hi @JGAlltricks , In addition to @Shahid12523  response, I am adding some more points.

Please check the below things to fix the issue

1. Please check the Fabric tenant settings “Service principal can use Fabric APIs” is enabled. This is required for Service principal to interact with Fabric resources.

2. Please assign the Service Principal to a workspace role Contributor or Member via the Manage Access option in the workspace.

3. Use the Lakehouse sharing UI to explicitly grant the Service principal, ReadData on the SQL Analytics Endpoint and ReadAll on the Lakehouse.

4. Use T-SQL to Grant SELECT, Once the Service principal has control plane access, you can grant data-level access using T-SQL.

GRANT SELECT ON [schema].[view_name] TO [service_principal_name];

Note: You cannot manually create users with CREATE USER unless you are using the FROM EXTERNAL PROVIDER clause and have Directory Readers role in Entra ID.

5. Use the below query to check what permissions the Service principal has.

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');

Note: Service principals cannot perform DCL operations like GRANT, REVOKE, or DENY themselves. These must be executed by a user with elevated permissions. Service principals cannot trigger implicit user creation, so check the user identity exists before assigning permissions.

Please refer Microsoft articles and community threads.

Authentication in SQL database - Microsoft Fabric | Microsoft Learn

Service Principals in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

SQL Granular Permissions - Microsoft Fabric | Microsoft Learn

Solved: How to Grant Access to an Individual Table/View in... - Microsoft Fabric Community

Solved: Fabric Lakehouse Permissions - Microsoft Fabric Community

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[JGAlltricks]

Hi @v-dineshya,

Thank's a lot for your answer. After cheking, everything were already done.

But I found a solution. I used the Onelake explorer from my desktop. I wen to the "Files" folder, and I "shared to everybody" that one.

Now, I'm able to run the SQL Query with the Service Principal.

[v-dineshya]

Hi @JGAlltricks ,

Thank you for the update. We are pleased to hear that you have found a solution. Please share the details here, as sharing the solution could help others in the community with similar issues. 

Regards,

Dinesh

[JGAlltricks]

Hi everyone,

I’ve been working with Microsoft Fabric and so far I’ve been able to:

• Write parquet files into a Lakehouse (remotely with python/azcopy),

• Define views with SQL remotely using OPENROWSET,

• To do that, I made connection using a Service Principal (App Registration).

Now I’m trying to query one of these views from the SQL Analytics Endpoint of the Lakehouse.
The view is based on parquet files stored under the Files folder of the Lakehouse.

When I execute a simple SELECT * query on that view via Python (using SQLAlchemy and ODBC, following this guide https://medium.com/@mariusz_kujawski/connect-to-microsoft-fabric-warehouse-using-python-and-sqlalche...), I only get back the columns of the view but 0 rows.

It feels like my Service Principal only has access to the schema (metadata) but not the underlying parquet data when executing queries.

If I run the exact same query from the Fabric web interface or Azure Data Studio (using my user account), the query works perfectly and returns the expected rows.

Has anyone run into this before? Do I need some specific permission to the Service Principal on the Lakehouse / SQL Endpoint ?

Thanks a lot for any guidance!

[Shahid12523]

Your Service Principal sees the schema but returns 0 rows because it lacks access to the underlying parquet data.

Fix:

Grant the SP Data Reader (or Contributor) role on the Lakehouse, including the Files folder.

Ensure it has access to the SQL Analytics Endpoint.

Optionally, add the SP to the workspace hosting the Lakehouse.

Once these permissions are set, queries via the SP will return data.