Error running a spark notebook reading a table from warehouse

[phktu]

My service principal has admin access to the workspace. I used that spn to create fabric conection in airflow. Lakehouse read and write works fine, when i try to read table from warehouse. I am getting error as below

Py4JJavaError: An error occurred while calling o5533.synapsesql. : com.microsoft.spark.fabric.tds.error.FabricSparkTDSInternalAuthError: HTTP request forbidden. Request Id - 25ef7e29-b7ac-404d-add5-d1148821d70b. at com.microsoft.spark.fabric.tds.utility.FabricTDSRestfulAPIClientv2$.sendHttpRequest(FabricTDSRestfulAPIClientv2.scala:183) at com.microsoft.spark.fabric.tds.utility.FabricTDSRestfulAPIClientv2$.submitAndProcessHttpRequest(FabricTDSRestfulAPIClientv2.scala:105) at com.microsoft.spark.fabric.tds.meta.FabricTDSEndPoint$.$anonfun$discover$9(FabricTDSEndPoint.scala:326)

If i run the same notebook via pipeline, i am not getting any issues. Anyone else facing issue while trying to run a notebook which access warehouse tables via airflow?
my code - 

config_df = spark.read.synapsesql("Warehouse.dbo.configuration")

[v-nmadadi-msft]

Hi @phktu 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[phktu]

open

[tayloramy]

Hi @phktu,

That FabricSparkTDSInternalAuthError: HTTP request forbidden means your notebook’s identity can reach the Lakehouse (OneLake/ABFS) but doesn’t have permission to the Warehouse SQL engine that sits behind spark.read.synapsesql("Warehouse.dbo.configuration"). Pipelines work because they typically run under a different identity that does have the required Warehouse SQL permissions.

 

1. Grant the service principal access at the Warehouse (item) level
   Give it at least “Read all data using SQL” on the Warehouse (or higher if needed). This is the Warehouse’s SQL-engine permission, separate from Lakehouse/OneLake permissions. See Microsoft’s connector permissions note here and a concise overview here.
2. Create a database user for the SPN and add it to roles (object-level)
   

   -- Read tables
   ALTER ROLE db_datareader ADD MEMBER [<your-spn-app-id-or-name>];
   
   -- Optional: allow INSERT/UPDATE/DELETE
   ALTER ROLE db_datawriter ADD MEMBER [<your-spn-app-id-or-name>];
       

   The connector requires item-level + object-level permissions to read tables. Microsoft documents this requirement here. Using a service principal with Warehouse is supported and documented here.
3. Confirm tenant setting for service principals
   In the Fabric Admin portal, ensure service principals are allowed to use Fabric (scope to your SPN’s security group if your org restricts this).

Once those are in place, your line should work:

config_df = spark.read.synapsesql("Warehouse.dbo.configuration")

why it happens

• Lakehouse vs Warehouse permissions: Lakehouse operations ride on OneLake permissions you already granted; the Warehouse connector (synapsesql) calls the SQL engine (TDS endpoint) which also needs SQL-style permissions. Without them, you get an HTTP 403/forbidden from the connector. See the Fabric Spark-to-Warehouse connector doc here.
• Pipeline works, Airflow doesn’t: Your Pipeline likely executes under an identity that already has Warehouse SQL access (for example, the user who created it or a configured app). Airflow runs with your service principal, which currently lacks Warehouse SQL permissions. Similar cases have been reported with 403/forbidden until the SPN was granted SQL engine access (community examples: example, and a recent thread here).
• Object-level matters: Even after giving “Read all data using SQL” at the item, you still need a database principal mapped and placed in db_datareader (and db_datawriter if you’ll write). A quick pattern is shown above and mirrors guidance such as this.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[phktu]

I can't create a user in fabric warehouse

CREATE USER [Alteryx to Azure Lakehouse/Data Warehouse (Service Principal)] FROM EXTERNAL PROVIDER;
I am getting the below error

Msg 22424, Level 16, State 0, Line 1
CREATE USER is not a supported statement type.

[v-nmadadi-msft]

Hi @phktu ,
Thanks for reaching out to the Microsoft fabric community forum.

Once warehouses are provided control plane permissions to an SPN through workspace roles or Item permissions, administrators can use T-SQL commands like GRANT to assign specific data plane permissions to service principals, to control precisely which metadata/data and operations an SPN has access to. This is recommended to follow the principle of least privilege.

GRANT SELECT ON <table name> TO <service principal name>;

 

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

[phktu]

SPN has admin access to workspace, I tried to run the script you provided, getting the below error

[tayloramy · Accepted solution]

Hi @phktu, 

I wonder if it has something to do with the special characters in your service principal's name? 

I just tried running the exact grant statement that you ran and it works in my environment: 

 Are you able to have the service principal renamed to remove special characters and spaces? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[v-nmadadi-msft]

Hi @phktu 

I wanted to check if you had the opportunity to review the information provided by @tayloramy .Please feel free to contact us if you have any further questions.

Thank you.

[tayloramy]

Hi @phktu, 

My mistake, I was misreading the documentation. 

• CREATE USER cannot be explicitly executed currently. When GRANT or DENY is executed, the user is created automatically. The user will not be able to connect until sufficient workspace level rights are given.

SQL Granular Permissions - Microsoft Fabric | Microsoft Learn

Just execute the grant statements and the user will be created automatically. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.