Power BI Services - Unable to render paginated report - Cannot create a connection to data source

Question

I have a large summary paginated report with about 1 Azure SQL data source and 50 datasets and hosted on Power BI Services with Premium Capacity.

Each dataset points to a stored procedure.

The report has been running fine for months and now out of the blue I keep on getting the error shown below. Problem is, it's intermittent, sometimes I get the error message - and sometimes not. What can this be? By going to the "Get Help" link does not help me at all. Server performance related maybe?

Answer 1

You would need to check the logs or detail report: "Troubleshoot Server & Database Connection Problems with Reporting Services" is clear:

Cannot create a connection to data source 'datasourcename'. (rsErrorOpeningConnection):

This is a generic error that occurs when the report server can't open a connection to an external data source that provides data to a report.
This error appears with a second error message that indicates the underlying cause.

So you need the "second error message" in order to "even start looking for the reason".

For instance, if it is "An error has occurred while establishing a connection to the server", you can check the SQL Server service status (sc query MSSQLSERVER).

---

Please note my DB is an Azure SQL DB not on premises.
Also, not using SSRS, using Paginated Report hosted in Power BI Services.

Then make sure the Azure SQL Database firewall rules are correctly configured to allow connections from Power BI Services. You can do this by adding the IP addresses of Power BI Services to the allowed list in the Azure portal.
And verify that the dataset within Power BI Services is correctly configured to use the Azure SQL Database, with the correct credentials saved or appropriately delegated.

If the Paginated Report is using DirectQuery to query the Azure SQL Database, performance and connectivity can be more sensitive to network issues and database performance. Evaluate if using imported data (with scheduled refreshes) is more appropriate for your use case, depending on the real-time data requirement and the complexity of the queries.

Utilize Azure's monitoring and logging features to track any issues. Azure Monitor and Azure SQL Database's diagnostic logs can provide insights into connectivity issues or performance problems.