Lakehouse federation -' on behalf of ' queries

[SanthoshKumarK]

Is it possible to achieve the following in a lake-house federation setup using Azure Databricks?

1. Establish an external connection (EC1) to an external data source (EDS) using the credentials of user U1.
2. Create a foreign catalog (FC1) utilizing EC1.
3. Grant access to the Microsoft Entra ID group (UG1) for FC1.
4. User U2, a member of UG1, queries data from FC1.
5. The external data source (EDS) receives the data fetch query as if it were from user U1, not U2.
6. Is there a way to configure the system so that EDS recognizes the query as coming from U2 instead of U1, similar to an ‘on behalf of’ authorization mechanism?

If this setup is not feasible, what is the closest solution to accurately reflect the querying user in the audit records of the external data source (EDS)?

[rangu]

The Connection for LHF is maintained through a foreign ID part of the External Data Source, for example if your EDS is SQL Server, you create a SQL Local ID and control permissions to the Databases through that ID. The LHF is configured using the SQL Local ID and shows the list of Databases/Schemas the Local ID has. Once LHF is setup, second layer of permissions can be managed within Databricks as well using local databricks group or AD groups that are synched in Databricks.

[SanthoshKumarK]

Thanks for explaining the authorization flow, @rangu . In the example mentioned, does Databricks support passing the user’s credentials to an external data source? For instance, can it pass the OAuth token for the user along with the externalID credentials, allowing the external system to identify which user is querying the data?