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!
Hi ,
You've basically laid out the two real trade-offs correctly, so let me try to add some clarity on picking between them.
The Lakehouse connector currently only supports OAuth 2.0 (organizational account), service principal isn't available on that connector surface yet. So your concern about the owner leaving the org is valid, that connection is tied to a real user identity and will break if that account gets disabled or loses access.
The SQL analytics endpoint connection (using SQL Server as the connection type) does support Service Principal, which solves the "owner leaves the org" problem since it's not tied to any individual. Your point about it being specific to one Lakehouse (needing the dbname prompted at creation) is accurate too, there isn't a generic "connect to any Lakehouse" option at the SQL endpoint level, each connection is scoped to a specific database.
Given those constraints, the common pattern is:
If you need broad reuse across many Lakehouses and can tolerate OAuth tied to an owner, the Lakehouse connector is simpler to set up, but you'd want that owner to be a dedicated service account (not a real employee's account) so it's not tied to any one person leaving.
If long-term stability and not depending on any user identity matters more, go with the SQL endpoint + Service Principal route, even though it means one connection per Lakehouse. The "more connections" concern is real but it's a manageable, static list versus a connection that can silently break when someone leaves.
A hybrid a lot of teams land on is creating a dedicated non-human service account, using that as the "owner" for Lakehouse connector connections where SPN isn't available, and reserving actual Service Principal auth for the workspaces/endpoints where it's supported (SQL endpoint, Warehouse, Pipelines). That way you're not tied to any one person either way, you're just choosing between OAuth-as-a-service-account and native SPN depending on which surface you're connecting through.
If minimizing the number of connections matters more to you than authentication type, the Lakehouse connector with a service account as owner is probably your best bet. If avoiding any user-identity dependency entirely is the priority, the SQL endpoint with Service Principal is worth the extra per-Lakehouse setup.