PowerBI Report Server can't connect sql server (basic auth) in Direct Query Mode

Question

I'm trying to connect to sql server using basic auth in PBIRS Direct query mode, it pass the connection test but when I open the report I get "We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly."
I tried to set environment variable but it doesn't work, also I'm not able to change the connection string to add "Encrypt=True;TrustServerCertificate=True".
Directquery is working on the desktop. It only not working in the server while rendering.
How I can solve this problem?

Answer 1

Hi @IsraMavaldi-8048

In Power BI Desktop:

Edit Data Source

Choose Database → SQL Authentication

Enter SQL username + password

Save PBIX

Upload to PBIRS

PBIRS will now use the stored SQL credentials.

Thanks.

Answer 2

For Power BI Report Server DirectQuery, the likely issue is the SQL Server certificate trust requirement during server-side rendering.

Supported facts for this scenario:

1. Power BI Report Server does not support OAuth-based authentication for DirectQuery.
2. Power BI DirectQuery reports must include Encrypt=True;TrustServerCertificate=True in the connection string, or PBI_SQL_TRUSTED_SERVERS must be applied on the Power BI Report Server.
3. If the report works in Power BI Desktop but fails only when rendered in PBIRS, the server-side trust configuration is the part to fix.

Actionable path:

1. On the Power BI Report Server machine, set the environment variable PBI_SQL_TRUSTED_SERVERS to include the SQL Server instance name.
   • The value supports a comma-delimited list.
   • It also supports * as a wildcard.
   • Example: contososql*,mysvr
2. Make sure the value matches the exact server name pattern used by the report connection.
   • mysvr matches mysvr
   • It does not match mysvr.microsoft.com
3. After changing the environment variable, restart the report server so the setting is picked up.
4. If the SQL Server uses a self-signed certificate, the more durable fix is to have the SQL Server administrators install a certificate from a well-known certificate authority.
5. If the report uses DirectQuery and the connection string cannot be edited manually, use a supported update path only if the report was built with:
   • Enhanced DataSet Metadata enabled
   • parameterized connections Only reports with parameterized connections and databases can be updated after publishing.
6. Verify the authentication configuration only if Kerberos/delegation is intended. The specific error text about Analysis Services can also appear when report server authentication/delegation is not configured correctly in distributed environments. In that case, RSWindowsNegotiate should be the first authentication type in rsreportserver.config, and Kerberos constrained delegation must be configured.

If Basic authentication is being used only for report server access, that does not remove the DirectQuery SQL trust requirement on PBIRS.

---

References:

• Power BI report data sources in Power BI Report Server
• Troubleshoot custom visuals in Power BI Desktop for Power BI Report Server
• Change data source connection strings in Power BI reports with PowerShell - Power BI Report Server
• Configure Kerberos to use Power BI reports