Site icon TheWindowsUpdate.com

Scheduled refresh issue with pbix report that connects to Access DB data source in PBIRS

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

This article discusses about the error “The login failed for data source ‘Unknown’ that occurs with scheduled refresh of pbix report that uses Access database as the data source and the solution to get the refresh to work.

 

Error message: Data source error: The login failed for data source ‘Unknown’

 

 

Clicking on    in the status, would give you details of the error:

 

 

Error details:

SessionID: f94a2716-08e8-47a3-87e8-668c92d4cae2
[0] -1055784932: DataFormat.Error: Unspecified error. ADB.accdb. ;Unspecified error. The exception was raised by the IDbCommand interface.

 

Though the test connection to data source succeeds, the refresh fails with the above-mentioned error.

 

 

How to get the refresh to work ?

 

For any pbix report that connects to Access db data source, you need to make sure the following steps are done to get the report refresh to work:

 

Step 1:  Make a note of the account used in the “User name” section of “Credentials” (Credentials required to connect to Access DB data source) of the pbix report for which the refresh failed. 

From the screenshot below, “abc” account is used to connect to the data source for my report.

 

 

 

Step 2:  Note down the service account used by PBIRS from “Service Account” section of Report Server Configuration manager of PBIRS

From the screenshot below, “Virtual Service account” is my PBIRS service account

 

 

Step 3: The account used to connect to the data source in pbix report (“abc” account obtained from step 1) needs to be provided permissions mentioned in the next step on the folder security of the “Temp” folder associated with the PBIRS service account (“Virtual Service account” obtained from step 2).

 

C:\Windows\ServiceProfiles\PowerBIReportServer\AppData\Local\Temp

C:\Windows\Users\<domain account> \AppData\Local\Temp

 

Step 4:   Provide the following permissions to the data source credential account “abc” in the folder security of PBIRS service account’s (Virtual service account) temp folder as shown below:

 

After implementing the steps mentioned above, you should see the refresh working successfully for the pbix report.

 

Why is providing permission to PBIRS Service account’s Temp folder for the data source account, required?

 

When performing scheduled refresh for reports that use Access DB as the data source, the Mash up process performs impersonation using the account provided in the data source credentials section and hence checks the PBIRS Service account’s folder security to validate if the data source credential account used in the report has the necessary privileges. This validation decides the success/failure of the report refresh.

 

Following is a screenshot from Process Monitor (Process Monitor - Windows Sysinternals | Microsoft Docs) trace that explains the behaviour:

 

Hope this article helps you in fixing your report refresh issue!   

 

 

 

Author:  Uma S M – Technical Advisor, SQL Server BI team, Microsoft
Reviewer: Kane Conway – Escalation Engineer, SQL Server BI team, Microsoft

 

Exit mobile version