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!
Super UserHi lskumardadi ,
The choice largely depends on your organization's authentication and governance strategy:
Lakehouse connection (OAuth): This is the native Fabric connection and is the recommended option when using user identity. However, as you mentioned, if the connection is tied to an individual user's OAuth credentials, it can become an operational risk if that user leaves the organization or their account is disabled.
SQL Server connection: This provides more flexibility and can use service principals or SQL authentication (where supported), but it requires specifying the Lakehouse (database) name. If you have many Lakehouses, you'll end up managing many separate connections, which increases administrative overhead.
For enterprise environments, a common approach is:
Use service principals or managed identities wherever Fabric supports them, instead of individual user accounts.
Store credentials centrally (for example, in Azure Key Vault where applicable) and automate connection deployment through CI/CD.
Accept multiple SQL connections if they are required, but manage them as infrastructure rather than manually. The additional connections are usually easier to maintain than connections that depend on a specific user's identity.
If you're using Data Factory or Fabric Pipelines, another consideration is whether your workloads can reference workspace items directly rather than relying on individual connections, as this reduces connection management.
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.
- ssrithar
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!
