Using Report Builder to DirectQuery Azure Databricks

[Node4Ben]

Hi,

Apologies this is a question on Report Builder, I realise this is the Desktop forum... but where do I go?!?! 😁

I am currently working on a solution where a customer has data in Azure Databricks Unity Catalog, with RLS configured in a "gold" schema. Due to the RLS in the source, we need to use DirectQuery in Power BI to access the data. The end user needs to use Report Builder to build paginated reports, and is comfortable using this tool due to history in SSRS.

For additional info, if it will help, we have configured a large serverless SQL warehouse cluster in databricks.

Also, the reports are being deployed to an F64 SKU (not a trial capacity).

The problem I am having is that in the service I don't appear to be getting any AAD passthrough, so the DirectQuery is accessing the source using the user credentials that were used to configure the connection, and not using the credentials of the user viewing/running the report.

Here is what I have done so far:

I have a cloud connection configured in the service to access the Azure Databricks instance. Azure Active Directory has been selected for authentication method and "Use SSO via Azure AD for DirectQuery queries" has been enabled. Privacy level is set to "Organisational". I currently don't have permission to confirm if the initial catalog has been configured; I don't know if this makes a difference.

I have created a paginated report that uses NativeQuery in the mashup as the user(s) wants to be able to write/add SQL to the M code, similar to the "Query" experience when connecting Report Builder to SQL Server. This report has been configured to use the cloud connection created in the previous point.

The report is published to the service, but due to the fact there is no semantic model, I cannot see that it is possible to enable the "Report viewers can only access this data source with their own Power BI identities using DirectQuery" option.

When we test the report, the report is running under my security context, as I configured the report and connection.

We can observe this in two different ways:

1) Each tester can only see the data I am able to see in the source, as I can only see a subsection of data due to RLS.

2) When looking at query history in databricks we can see the "User" and the "Run as" user are both recorded as me.

I have looked at the Databrick specific documentation, and there is a suggestion that account SSO needs to be enabled, which I need to check the status of.

(I realise that the image says AWS, but I'm working on the basis that this could be the same requirement in Azure?)

I have also read that potentially a VNet data gateway may be required Use virtual network data gateway and data sources in Power BI | Microsoft Learn, however as I am unable to configure Data Source Settings, due to the use of Paginated Reports and DirectQuery, I'm unsure if this will work.

All documentation seems to be focused on Power BI Desktop (e.g., Connect Power BI to Azure Databricks - Azure Databricks | Microsoft Learn), and the Report Builder documentation that is available on this subject is minimal and brief at best.

One final thing to mention, creating an intermediary semantic model, that is connected to the source, that Report Builder could connect to is not a viable option.

Whilst it may be a solution, and one I am yet to test!, it will not be a solution that will be accepted by the report developer(s).

Has anybody come across this issue before, specifically with Paginated Reports when using DirectQuery to query Unity Catalog schemas in Azure Databricks, and if so please could you offer some advice.

Thanks in advance for any help that can be offered ✌️

[Poojara_D12]

Hi @Node4Ben 

Your issue stems from the fact that Report Builder does not support Azure Active Directory (AAD) Single Sign-On (SSO) passthrough when connecting to Azure Databricks via DirectQuery. Unlike Power BI, where semantic models allow enforcing user-level authentication, paginated reports using DirectQuery operate differently. Since there's no semantic model to enable "Report viewers can only access this data source with their own Power BI identities," the connection defaults to the credentials used when configuring the data source. This is why all users see data as if they were you. A potential solution is enabling Account SSO in Databricks, which may allow identity propagation, though this needs confirmation. Additionally, a VNet data gateway could be required if your Databricks environment is within a secured network. However, Report Builder’s native approach to querying databases, especially when using DirectQuery, does not fully integrate with Power BI’s authentication model. If an intermediary semantic model is not an option, you may need to explore whether service principals or Databricks Unity Catalog access policies can be leveraged for dynamic security enforcement.

[Node4Ben]

Thank you for your response.

I can confirm that the SSO is configured correctly.

I tested this by deploying a Power BI Desktop report, with semantic model, and configured the semantic model to use the configured cloud connection that I was trying to use with the paginated report. For me, the new report showed only data that I can access, and for other testers it showed the data that they were only permitted to see in the source. This also reaffirmed that the RLS was configured correctly in the source.

I did create a connection that used a pre-configured vnet gateway, with SSO enabled for DirectQuery queries, but the paginated report crashed/failed. To be fair, this is a documented limitation of paginated reports (Connect paginated reports to data sources using Power Query - Power BI | Microsoft Learn) however I overlooked this in my research. The VNet Gateway connection did work successfully with the test semantic model, and RLS was adhered to in the source when using this connection.

Having confirmed that the current cloud connections work (in Power BI Desktop/semantic models), and the VNet Gateway option doesn't work with paginated reports, I have now logged this with Microsoft support.

[Anonymous]

Hi @Node4Ben ,

We have moved your post to the corresponding Report Server forum. There will be more users or related posts here to help you. The following are initial suggestions.
First, make sure your Azure Databricks instance has AAD Single Sign-On (SSO) properly configured. This is key to passing user credentials through DirectQuery. Second, the “Report viewers can only access this data source through DirectQuery using their own Power BI identity” option cannot be enabled without a semantic model. This is a limitation when using paged reports in Power BI. You can consider alternative ways to manage row-level security (RLS) directly in Databricks.
You can also get more help by reviewing the following documentation

RLS in Databricks Unity Catalog and Power BI | Miles Cole
Utilize Power BI Semantic Model within Power BI Re... - Microsoft Fabric Community

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly