Troubleshooting SQL Server Connections in Power BI Report Server (May 2024)

Error appears with Direct Query and connection string is connected Successfully.

Error appear once you are loading the report

Introduction

In May 2024, Power BI Report Server introduced new measures to enhance security and streamline SQL Server connections. Users may encounter errors when using Power BI Desktop due to these updates. This article provides a comprehensive guide to troubleshoot these issues effectively.

Troubleshooting Steps

1. Modify Environment Variable

• Adjust the value of the environment variable PBI_SQL_TRUSTED_SERVERS to include the SQL Servers.
• The supported values align with those outlined for gateway configuration.

2. Options for Connections to SQL Server

• Add Environment Variable: Follow the solution mentioned previously to add the environment variable PBI_SQL_TRUSTED_SERVERS.
• Acquire a Certificate: Request your SQL administrators to obtain a certificate from a well-known certificate authority.

Conclusion

By following these steps, users can resolve connection issues and ensure seamless integration with SQL Server in Power BI Report Server. These measures not only enhance security but also improve the overall user experience.