Scheduled refresh issue with pbix report that connects to Access DB data source in PBIRS
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). If you have a built-in-account configured as the service account for PBIRS (such as Virtual Service, Network Service, Local System), you should look for Temp folder in the following location on PBIRS Server: C:\Windows\ServiceProfiles\PowerBIReportServer\AppData\Local\Temp If you have a domain account configured as the service account for PBIRS, you should look for Temp folder in the following location on PBIRS Server: 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: Read & Execute List folder contents Read Write 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
