Unable to connect Fabric Lakehouse using SQL Endpoint in MS Report Server

[ssesham]

Unable to connect Azure SQL DB in Fabric from Report Server, I am getting below error. Whereas i am able to connect the same database from report builder.

Error:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=52; handshake=14946;

[v-jianpeng-msft]

Hi, @ssesham 

This error you are experiencing may have occurred due to a connection or query issue. The connection timed out because the application was unable to connect to the server. You can find the same bugs as you in the official documentation below, and you need to follow the steps to resolve your current issue:

Troubleshoot common connection issues - Azure SQL Database & Azure SQL Managed Instance | Microsoft ...

Troubleshoot common connection issues - Azure SQL Database & Azure SQL Managed Instance | Microsoft ...

Troubleshoot common connection issues - Azure SQL Database & Azure SQL Managed Instance | Microsoft ...

In addition, you can also follow the following documentation to resolve the issue of the current connection timeout:

Timeout expired messages when connecting to SQL Server - SQL Server | Microsoft Learn

Since Report Server only supports windows authentication and database user authentication, you need to make sure that the average you provide can access your Azure SQL Server Database normally:

You'll also need to make sure you're entering the correct connection string according to the document below:

Create data connection strings in Report Builder - SQL Server Reporting Services (SSRS) | Microsoft ...

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 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?

[v-jianpeng-msft]

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.

[v-jianpeng-msft]

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.

[v-jianpeng-msft]

Hi, @ssesham 

Thank you very much for your reply. Due to the limitations of Report Server. We can't call OAuth2 authentication directly in the Report Server portal and then access the contents of the Fabric. We can do this in two ways:
1. Try your data source to use a data source that is supported by Report, please note that you should choose a data source that can be accessed using Windows or database user authentication.
2. Create a new web page based on your website, publish this paginated report to Power BI Service, and then use Power BI embedded to embed this paginated report into your new web page.
During the embedding process, you can use service principal validation. Service principal authentication without requiring the user to sign in to Power BI Service.
Documentation related to paginated report embedding can be found at the following link:

Embed paginated reports in your Power BI embedded analytics application - Power BI | Microsoft Learn

Embed content in your Power BI embedded analytics application - 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, much appreciated your effort on this. But this is not what I am expecting. One thing doesn't make sense to me, when I am able to connect Fabric from MS Report Builder by why not from Report Server. If this option is still not provided by MS means, then I will find some other way.

[v-jianpeng-msft · Accepted solution]

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:

Configure custom or forms authentication on the report server - SQL Server Reporting Services (SSRS)...

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.