Power BI Report Server Connection to SQL Server Real-time Data Fails After Publishing

Hi Anonymous ,

Thanks for reaching out to the Microsoft fabric community forum. 

This is a common issue when publishing Power BI reports to Power BI Report Server (PBIRS) that rely on live connections or direct query to SQL Server Analysis Services (SSAS) or SQL Server. Even though the connection works in Power BI Desktop, the report may fail after publishing due to server-side configuration or authentication problems.

Can you please follow below steps:

1. Check Data Source Credentials in Report Server:
After publishing, PBIRS does not carry over your local credentials. You need to: Go to Report Manager (Web Portal).
Find your published report. Click the three dots (…) > Manage > Data Sources. Re-enter or configure the data source credentials, and ensure: The connection string is correct. You're using the appropriate authentication method:
Windows integrated security (if using domain account). Stored credentials (recommended for scheduled refresh or gateway scenarios). The user has permission on the SQL Server database.

2. Check Server Configuration for Live/DirectQuery:
PBIRS supports live connections and DirectQuery, but you need to make sure: SQL Server or SSAS allows remote connections.
The service account running the Report Server has access to the data source. The Kerberos delegation is configured correctly if using Windows credentials (for double-hop authentication scenarios).

3. Validate Connection String:
Even a minor mismatch in the connection string can cause this issue. Common things to check: Use the server name or FQDN (ServerName\Instance or FullyQualifiedDomainName). Avoid localhost or . which may not resolve correctly from the server.
Make sure you specify the correct authentication method if using a SQL login.

4. Report Server Logs:
To get deeper insights, check the PBIRS logs:

Default location:
C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

Look for the specific Request ID (9852fabe-...) in the log files. That will show detailed errors about why the connection failed (e.g., login failed, server not reachable, etc.).

5. Firewall and Network Access:
Ensure the PBIRS machine can reach the SQL Server instance on the network. Port 1433 (default for SQL Server) must be open between PBIRS and SQL Server.

6. Service Account Permissions:
The PBIRS service account must have at least read permissions on the SQL database or SSAS cube.

7. Switch to Embedded Data or Import Mode:
If live connection isn't strictly needed, you might consider using Import mode with scheduled refresh. This avoids many of the real-time connection issues.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards