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

IsraMavaldi-8048 0 Reputation points
2026-07-30T10:15:54.3033333+00:00

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?

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.

0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-07-30T10:16:12.81+00:00

    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:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Senthil kumar 2,410 Reputation points
    2026-07-30T11:48:56.6566667+00:00

    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.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.