creating a lakehouse connection using service principal

[mrigank_1]

how to create a new connection to a lakehouse inside my fabric environment using service principal?

[v-shamiliv · Accepted solution]

Hi @LarsKemmann @carlosgeovany 

Thanks for the screenshots and clarification they’re really helpful.

You’re right, currently Dataflow Gen2 doesn’t support Service Principal authentication when connecting to a Lakehouse. The only available option in the UI is using an Organizational Account, even if your Service Principal is an Admin in the workspace.

Service Principals can be used to access a Lakehouse via Notebooks or APIs by using Azure AD tokens, but not yet through Dataflow Gen2. The error you're seeing( 24559 DML statements not supported) is likely due to this limitation — Dataflows require a user-based connection to perform write operations.

Your workaround using Azure SQL is creative, but writing to Lakehouse from Dataflows still requires a user context, so it runs into the same issue.

For now, we recommend using a user account for Dataflow connections. If you need to automate using a Service Principal, consider using a Notebook instead

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[v-shamiliv]

Hi @mrigank_1 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @mrigank_1 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @mrigank_1 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @mrigank_1 
Thank you for reaching out microsoft fabric community forum.

To connect to a Lakehouse in Microsoft Fabric using a Service Principal, follow these steps:

1. Register a Service Principal

   • In Azure Portal, go to Azure AD > App registrations, register an app, and note the Client ID, Tenant ID, and Client Secret.

2. Grant Access in Fabric

   • Go to your Fabric workspace, open Settings > Permissions, and add the Service Principal with a Member or Admin role.

3. Create the Connection

   • When creating a new connection (in a Dataflow Gen2, Pipeline, or Notebook), select Lakehouse as the target.

   • Choose Service Principal authentication, and enter the Tenant ID, Client ID, and Client Secret.

   • Save and test the connection.
     
     If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
     Thank you.

[carlosgeovany]

Hi @v-shamiliv 

I'm having the same issue here, trying to connect to the Lakehouse but I don't see the option to use service principal, only organizational account.

My service principal is and admin int the workspace:

I even tried the Azure SQL Server DB connector and it create the connection and load the tables and schemas:

but when publish the Dataflow got this error 24559 DML statemements:

[LarsKemmann]

@v-shamiliv Did you see this reply from @carlosgeovany? Your suggested solution does not work. I can confirm; I have the same issue as @carlosgeovany -- there's no option to sign in with anything other than an organizational account when creating a new connection in this context. What are we missing?