Sql-server – Power BI report server: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ when trying to view a report

authenticationpowerbi-report-serversql server

We have SQL Server 2019 and Power BI Report Server installed on the same host. we're using a virtual service account for Power BI Report Server. We get the following error when trying to view a report:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.

We are trying to migrate and upgrade to SQL Server 2019 and Power BI Report Server on Windows Server 2019 from SQL Server 2014 and SSRS on Windows 2012. SQL Server 2019 has been set up on the new host and database schemas have been migrated over and Power BI Report Server has been installed (same host as the new SQL Server). We can access the database using SSMS without any issues. We are able to log in to the reports URL (https://hostname/reports) and can see that there are some reports that have been created. When we try to view a report, we get the error I mentioned above.

We are using a virtual service account for Power BI Report Server. As mentioned, Power BI Report Server and SQL Server are on the same host.

For Data source properties > Credentials > Log into the data source
If we set it to "As the user viewing the report" and click the "Test connection" button, it says "Connected successfully"
Also tried setting it to "By prompting the user viewing the report for credentials" with "Type of credentials" set to "Windows username and password", but it does not prompt for credentials when trying to view a report.
In both cases we get the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' " error.

web.config has

    <authentication mode="Windows" />
    <identity impersonate="true" />

rsreportserver.config has

        <AuthenticationTypes>
            <RSWindowsNTLM/>
        </AuthenticationTypes>

We have also tried RSWindowsBasic and RSWindowsNegotiate, but they did not work.

We have 2 other servers where SQL Server and SSRS (not Power BI Report server) are installed and working. web.config and rsreportserver.config have the same values as above. I can't find any difference in the configs.

Some posts and documentation mention setting up SPNs, but as I understand it, SPNs are only needed if you use a domain account for the service account and not needed if you use the virtual service account. Maybe I misunderstood? Also read the posts about it possibly being a double hop issue, but the solution there seems to be to set up SPNs, but I'm not sure how to do that for the virtual service account.

Two of the docs I referred to:

Configure Windows Authentication on the Report Server

Specify Credential and Connection Information for Report Data Sources

Best Answer

Not familiar with virtual service accounts, but this is often related to Kerberos authentication.
Try using Microsoft® Kerberos Configuration Manager for SQL Server® to see if it can generate the SPNs for you.
It's a rather easy to use tool from Microsoft which will detect your SPN settings, and create scripts you can execute to fix your issues.

Related Question