Forum Discussion
Choosing connection types for lakehouse
Hi lskumardadi,
This is a great question, and I think it highlights one of the current design considerations in Fabric.
Today, Lakehouse connections are scoped to a specific Lakehouse, so if each project owns its own Lakehouse, it's expected that the number of SQL Analytics Endpoint connections will grow. At the moment, there isn't a concept of a single generic SQL connection that can dynamically target multiple Lakehouses.
From an enterprise perspective, I've generally seen customers follow these practices:
Use service principals or managed identities (where supported) instead of personal OAuth accounts for production workloads.
Treat connections as environment infrastructure (DEV/QA/PROD) and provision them through CI/CD or Infrastructure-as-Code, rather than creating and maintaining them manually.
Maintain one connection per Lakehouse only when the Lakehouse represents a separate security or ownership boundary. Although this increases the connection count, it provides clear governance, auditing, and least-privilege access.While having 15, 50, or even 100+ connections may initially seem excessive, the operational overhead becomes much smaller once connection creation and deployment are automated.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Hello,
Thank you for your prompt response.
I understand the recommendation to use Service Principal authentication through a SQL Server/SQL Analytics Endpoint connection for production workloads. However, in our scenario we have multiple data projects, and each project has its own dedicated Lakehouse.
If we use SQL Server/SQL Analytics Endpoint connections, the connection is tied to a specific Lakehouse (database). As a result, the number of connections grows significantly across environments.
For example:
- Dev: 5 Lakehouses = 5 connections
- QA: 5 Lakehouses = 5 connections
- Prod: 5 Lakehouses = 5 connections
This results in a total of 15 connections to manage, and the number will continue to increase as more projects and Lakehouses are added.
Given this scenario, what would be the Microsoft-recommended best practice?
- Should we continue with SQL Analytics Endpoint connections and manage the larger number of connections through automation/CI-CD?
- Or is there a recommended way to create Lakehouse connections that are not dependent on an individual user's OAuth identity (for example, using a service account, managed identity, or service principal)?
- How do customers with a large number of Lakehouses typically handle connection management in Fabric?
We are trying to balance governance and service account independence against the operational overhead of managing a growing number of Lakehouse-specific connections.
Thanks in advance for your guidance.
Super UserHi lskumardadi,
This is a great question, and I think it highlights one of the current design considerations in Fabric.
Today, Lakehouse connections are scoped to a specific Lakehouse, so if each project owns its own Lakehouse, it's expected that the number of SQL Analytics Endpoint connections will grow. At the moment, there isn't a concept of a single generic SQL connection that can dynamically target multiple Lakehouses.
From an enterprise perspective, I've generally seen customers follow these practices:
Use service principals or managed identities (where supported) instead of personal OAuth accounts for production workloads.
Treat connections as environment infrastructure (DEV/QA/PROD) and provision them through CI/CD or Infrastructure-as-Code, rather than creating and maintaining them manually.
Maintain one connection per Lakehouse only when the Lakehouse represents a separate security or ownership boundary. Although this increases the connection count, it provides clear governance, auditing, and least-privilege access.
While having 15, 50, or even 100+ connections may initially seem excessive, the operational overhead becomes much smaller once connection creation and deployment are automated.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!