topic Re: Fabric Warehouse - Service Principal Connection in Data Warehouse

Fabric Warehouse - Service Principal Connection

WDarwish — Sun, 26 Nov 2023 14:19:00 GMT

I want to build an application to write data into a Fabric Warehouse.

This article says that there are 2 ways to connection to a Fabric Warehouse SQL Endpoint:

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

In Microsoft Fabric, two types of authenticated users are supported through the SQL connection string:

Microsoft Entra ID (formerly Azure Active Directory) user principals, or user identities
Microsoft Entra ID (formerly Azure Active Directory) service principals

I am unable to find any guidance on how to configure the service principal connectivity.

Using a user principal via MFA authentication is not possible in my scenario.

Can someone please provide a step-by-step process to create the service principal, give it the necessary permissions in the Fabric WH, and use it to connect via the SQL endpoint?

Re: Fabric Warehouse - Service Principal Connection

AndyDDC — Sun, 26 Nov 2023 14:37:22 GMT

hi WDarwish @I don't think there's any specific documentation in the fabric docs, but you can follow this guide here to create a service principal https://learn.microsoft.com/en-us/purview/create-service-principal-azure

then you give the service principal the required permissions in the workspace or on the warehouse itself.

then to use the service principal is going to depend on your application

Re: Fabric Warehouse - Service Principal Connection

WDarwish — Sun, 26 Nov 2023 14:39:50 GMT

I actually created the principal as per this very same guide. I cannot get that principal to show up within Fabric WH when sharing or setting permissions.

Re: Fabric Warehouse - Service Principal Connection

AndyDDC — Sun, 26 Nov 2023 15:49:18 GMT

Maybe this blog will help?

How to use service principal authentication to access Microsoft Fabric's OneLake - Sam Debruyn

Re: Fabric Warehouse - Service Principal Connection

WDarwish — Mon, 27 Nov 2023 15:03:58 GMT

Great find! Exactly what I was after. Managed to authenticate using the principal and got things running. Thank you.

Re: Fabric Warehouse - Service Principal Connection

kjellvs — Mon, 26 Feb 2024 18:54:26 GMT

I am trying to achieve the exact similar thing, however when authenticating with

ClientSecretCredential in python, I am unable to write to the warehouses. I am able to read from the same warehouses though.

More specifically, I get the following Error:
The INSERT permission or external policy action 'Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Rows/Insert' was denied on the object '[table]', database '[warehouse name]', schema 'dbo'.

When using AzureCliCredential, this problem does not persist.

I have tried making the service principle admin (similar to my az credentials), but that also did not help.

Any clue how to have the service principle write to the warehouse?

kind regards,

Kjell

Re: Fabric Warehouse - Service Principal Connection

AndyDDC — Mon, 26 Feb 2024 19:56:26 GMT

Hi kjellvs how are you trying to write to the Warehouse? If you are trying to write to the storage directly it won't work, inserts into the warehouse can only be done via the sql endpoint of the warehouse - just checking to see how you're inserting so please forgive me if you already know this

Re: Fabric Warehouse - Service Principal Connection

kjellvs — Tue, 27 Feb 2024 07:10:17 GMT

Hi Andy, thank you for the reply. I was indeed trying to write via the SQL endpoint.

I found a fix by giving:
GRANT INSERT ON SCHEMA::dbo TO public;

I didn't know I had to configure these additional rights.

Kind regards,

Kjell