Forum Discussion
Helper IUnable to connect Fabric Lakehouse using SQL Endpoint in MS Report Server
Hi, ssesham
Thank you very much for your reply. I have found another solution for you. You can consider the following two solutions:
First, you can use your Azure SQL Server database directly instead of using Fabric hosted Azure SQL Server database.This way you can authenticate with Database User in Report Server and then view your reports normally.If you don't want to connect directly to your Azure SQL Server database, you have the following option:
You can make custom authentication extensions to implement authentication to Azure SQL Server database that has Fabric hosting.
For documentation on custom identity extensions, you can refer to this link below:If you are not familiar with custom identity extensions, you can refer to the examples provided by Microsoft:
Reporting-Services/CustomSecuritySample at master · microsoft/Reporting-Services · GitHub
The second method you need to have an in-depth understanding of programming and then practice it.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IThanks Li for the reply.
1) Query Issue: Report is running fine in report builder (which is in my local machine), issue is happening when i uploaded this report in report server (on-prem).
2) Connectivity Issue: I am able to connect the source Dataset (SQL EndPoint in Fabric) from my SSMS and also from other tools like (Power bi Report builder, Power Bi Desktop).
So I am not very much sure, whether I can connect Az SQL DB in Fabric from my Report server or not?
Hi, ssesham
Are you connecting to Lake house in Power BI Report Builder as shown in the following image:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- ssesham
Hi
As I mentioned in my thread, I don't have issue in connecting from Power bi Report Builder. I am having issue in connecting from Report Server to my SQL DB which is in Az Fabric.
Hi, ssesham
Both data sources are supported in Power BI Report builder though. This is because in Power BI Report builder is able to invoke OAuth2 authentication to access the Azure SQL Server database dragged over by Fabric.
In Report Server, data sources only support Windows or database user account authentication. So there will be a problem when you upload to Report Server.
You can publish your paginated report to Power BI service and then test to see it.Supported data sources for Power BI paginated reports - Power BI | Microsoft Learn
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- ssesham
Thanks Li again.
I am able to publish the report in Power BI Service. But I want to publish the report in Report Server (On-Prem), the reason is we have some users who are using on-prem Power BI Report server and few are using Power BI services. To save the Licensing cost we didn't opt for power bi premium service for all the users. That is the reason we wanted to keep some users to use on-prem server.