Fabric Cloud Connection for Paginated Reports

[dimitragav10_]

Hello,

I have migrated some SSRS reports to Microsoft Fabric. In the RDL files, I configured a data source pointing to an Azure SQL Database, using the connection string and database associated with a Fabric Lakehouse.

Here’s the architecture I am aiming for:

• The Lakehouse is located in Workspace A.

• The SSRS reports are published in Workspace B.

Access Requirements:

• Viewers in Workspace B should be able to view the reports.

• Contributors in Workspace B should be able to view existing reports and create new reports based on the Lakehouse, but should not have any permissions on the Lakehouse or access to Workspace A.

To achieve this, I attempted to create a Cloud Connection using a Service Principal for authentication. However, I noticed the following behavior:

• When SSO (Single Sign-On) is disabled, the reports fail to load data.

• When SSO is enabled, the reports work as expected.

I would appreciate some guidance on what the correct authentication approach should be to meet these security and access requirements. Specifically, I want users in Workspace B to interact with reports without granting them direct access to the Lakehouse or Workspace A.

Thank you

[v-pbandela-msft]

Hi @dimitragav10_,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @dimitragav10_,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @dimitragav10_,

Thank you for reaching out in Microsoft Community Forum.

Thank you @Akash_Varuna , @GilbertQ    for the helpful response.

As suggested by Akash_Varuna,GilbertQ.,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[Akash_Varuna]

Hi @dimitragav10_  For this, you will need a Service Principal with Reader access to the Lakehouse in Workspace A, as it is an Azure SQL-based setup. Configure a Cloud Connection in Workspace B using the Service Principal and ensure SSO is enabled for authentication. Assign Viewer and Contributor roles in Workspace B, ensuring Contributors can create reports while data access is routed through the Service Principal. Finally, restrict direct access to Workspace A for all users in Workspace B to maintain security boundaries.

[GilbertQ]

Hi @dimitragav10_ 

Because the query needs to run against the Lakehouse SQL Endpoint to run successfully, you will need to grant the users access to read from the Lakehouse SQL Endpoint.

Here are details how to do this where you would add "Read all SQL Data Endpoint"

Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

[dimitragav10_]

Hello @GilbertQ , 

Thank you for your reply!
Is there a way to configure it without giving direct access to users ? I tried authentication via Service principal without the SSO enabled and doesnt work as expected. 

[v-pbandela-msft · Accepted solution]

Hi @dimitragav10_,

Thank you for reaching out in Microsoft Community Forum.

Currently, Service Principal authentication is not supported for accessing Lakehouse SQL endpoints in RDL reports. That’s why disabling SSO with a Cloud Connection doesn’t work.

please follow below steps To meet your access and security requirements:

1.Enable SSO on the Cloud Connection used in the RDL report.

2.In Workspace A, go to the Lakehouse > Manage Access.

3.Grant users (or an AAD group) the "Read All SQL Data" permission only.

->This allows query access via the SQL endpoint.

->Users won’t have access to the Lakehouse UI or Workspace A.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.