Forum Discussion

vmattard's avatar
vmattard
Regular Visitor
11 months ago
Solved

Service Principal Getting "HTTP request forbidden" When Reading from Data Warehouse

Service Principal Getting "HTTP request forbidden" Error When Reading from Data Warehouse via API-Triggered Notebook

 

Hi,

 

I'm experiencing a permissions issue when using a service principal to execute a Fabric notebook via API. The notebook successfully reads from a Lakehouse but fails when trying to read from a Data Warehouse in the same workspace, throwing an "HTTP request forbidden" error.

 

Environment Details
  • Fabric Workspace: Same workspace for notebook, lakehouse, and data warehouse
  • Execution Method: API-triggered notebook execution using service principal
  • User Roles: I'm admin/creator, colleagues have Contributor role
  • Service Principal Role: Contributor role in workspace

 

Working Scenarios:
  • Manual execution by me (admin/creator) - works perfectly
  • Manual execution by colleagues with Contributor role - works perfectly  
  • Service principal querying DW locally (outside of Fabric notebook) - works perfectly
  • Service principal reading from Lakehouse in the notebook - works perfectly
Failing Scenario:
  • Service principal executing notebook via API and trying to read from Data Warehouse - fails with error
Error Details
ERROR FabricSparkTDSImplicits$FabricSparkTDSRead [Thread-68]: Error processing read request - HTTP request forbidden.
 
Code snippet
import com.microsoft.spark.fabric
from com.microsoft.spark.fabric.Constants import Constants


view_name = "MyWarehouse.dbo.my_view"
table_name = "MyWarehouse.dbo.my_table"
database_name = "MyWarehouse"


# This line fails when executed by service principal via API
df_table = spark.read.option(Constants.DatabaseName, database_name).synapsesql(f"SELECT * FROM {table_name}")
# or
df_table = spark.read.synapsesql(table_name)
print(f"Table count is {df_table.count()}")


df_view = spark.read.option(Constants.DatabaseName, database_name).synapsesql(f"SELECT * FROM {view_name}")
# or
df_view = spark.read.synapsesql(view_name)
print(f"View count is {df_view.count()}")
 
About The Service Principal:
In the Azure Service Principal
  • Capacity.Read.All
  • Lakehouse.ReadWrite.All
  • Tenant.Read.All
  • Warehouse.ReadWrite.All
  • Workspace.Read.All
In the Fabric Warehouse
  • SQL Permissions: SELECT, INSERT, UPDATE granted
  • Database Roles: db_datareader, db_datawriter
  • Additional Access: "Manage Connections and Gateways" granted for the Data Warehouse

 

Questions

  1. Is there a specific permission or configuration missing for service principals to access Data Warehouse through Spark in API-triggered notebooks?
  2. Are there differences in authentication context between manual execution and API-triggered execution that could cause this issue?
  3. Could this be related to connection pooling or session management when using service principals in Fabric notebooks?

 

What I've Tried
  • Verified all standard permissions are in place
  • Confirmed service principal works locally for DW queries
  • Confirmed lakehouse access works in the same notebook
  • Verified workspace contributor role is assigned


Any insights or suggestions would be greatly appreciated!

 

Vincent

data warehouse
Warehouse
  • v-prasare's avatar
    v-prasare
    11 months ago

    Hi vmattard,

     

    In Fabric, workspace roles (Admin, Member, Contributor, Viewer) govern high-level operations (create/delete/edit items). However, Spark synapsesql() queries check the item-level permission (Build/Read/Contributor) on the specific Data Warehouse.

    That means:

    • A service principal with workspace Contributor can create/edit items in the workspace.

    • But unless you go into the Data Warehouse, Manage permissions pane and grant the service principal Build (or Contributor) on that warehouse item itself, Spark-to-DW queries from the notebook API will still get blocked.

    That’s why you see:

    • Lakehouse access works (because Lakehouse Contributor is implied by workspace Contributor).

    • Warehouse access fails (because Spark uses the DW’s item ACL, not just workspace role).

    please refer this doc for more:

    https://learn.microsoft.com/en-us/fabric/data-warehouse/service-principals

     

     

     

    Thanks,

    Prashanth

    MS Fabric community support

4 Replies

  • v-prasare's avatar
    v-prasare
    Community Support
    11 months ago

    Hi vmattard,

     

    In Fabric, workspace roles (Admin, Member, Contributor, Viewer) govern high-level operations (create/delete/edit items). However, Spark synapsesql() queries check the item-level permission (Build/Read/Contributor) on the specific Data Warehouse.

    That means:

    • A service principal with workspace Contributor can create/edit items in the workspace.

    • But unless you go into the Data Warehouse, Manage permissions pane and grant the service principal Build (or Contributor) on that warehouse item itself, Spark-to-DW queries from the notebook API will still get blocked.

    That’s why you see:

    • Lakehouse access works (because Lakehouse Contributor is implied by workspace Contributor).

    • Warehouse access fails (because Spark uses the DW’s item ACL, not just workspace role).

    please refer this doc for more:

    https://learn.microsoft.com/en-us/fabric/data-warehouse/service-principals

     

     

     

    Thanks,

    Prashanth

    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago

    Hi vmattard,We would like to confirm if  your query got resolved or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago

    Hi @vmattard,We would like to confirm if  your query got resolved or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago

    May I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
    If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
    Thank you for being part of the Microsoft Fabric Community.