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

Published Jul 12 2021 12:37 AM 573 Views

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’

 

1.jpg

 

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

 

3.jpg

 

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.

 

4.png

 

 

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

 

5.png

 

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

6.jpg

 

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:

7.png

 

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

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2537178%22%20slang%3D%22en-US%22%3EScheduled%20refresh%20issue%20with%20pbix%20report%20that%20connects%20to%20Access%20DB%20data%20source%20in%20PBIRS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2537178%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20article%20discusses%20about%20the%20error%20%E2%80%9CThe%20login%20failed%20for%20data%20source%20%E2%80%98Unknown%E2%80%99%20that%20occurs%20with%20scheduled%20refresh%20of%20pbix%20report%20that%20uses%20Access%20database%20as%20the%20data%20source%20and%20the%20solution%20to%20get%20the%20refresh%20to%20work.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EError%20message%3C%2FU%3E%3A%20Data%20source%20error%3A%20The%20login%20failed%20for%20data%20source%20%E2%80%98Unknown%E2%80%99%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.jpg%22%20style%3D%22width%3A%20615px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294948i303A4B3D40E03DE4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221.jpg%22%20alt%3D%221.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClicking%20on%26nbsp%3B%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.jpg%22%20style%3D%22width%3A%2031px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294954i9B547BC5B1C2B9E1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222.jpg%22%20alt%3D%222.jpg%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%20in%20the%20status%2C%20would%20give%20you%20details%20of%20the%20error%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.jpg%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294955i6F552DA2900C2524%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223.jpg%22%20alt%3D%223.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EError%20details%3A%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ESessionID%3A%20f94a2716-08e8-47a3-87e8-668c92d4cae2%3CBR%20%2F%3E%5B0%5D%20-1055784932%3A%20DataFormat.Error%3A%20Unspecified%20error.%20ADB.accdb.%20%3BUnspecified%20error.%20The%20exception%20was%20raised%20by%20the%20IDbCommand%20interface.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThough%20the%20test%20connection%20to%20data%20source%20succeeds%2C%20the%20refresh%20fails%20with%20the%20above-mentioned%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EHow%20%3C%2FU%3E%3C%2FSTRONG%3E%3CSTRONG%3E%3CU%3Eto%20get%20the%20refresh%20to%20work%20%3F%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20any%20pbix%20report%20that%20connects%20to%20Access%20db%20data%20source%2C%20you%20need%20to%20make%20sure%20the%20following%20steps%20are%20done%20to%20get%20the%20report%20refresh%20to%20work%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EStep%201%3C%2FU%3E%3C%2FSTRONG%3E%3A%26nbsp%3B%20Make%20a%20note%20of%20the%20account%20used%20in%20the%20%E2%80%9CUser%20name%E2%80%9D%20section%20of%20%E2%80%9CCredentials%E2%80%9D%20(Credentials%20required%20to%20connect%20to%20Access%20DB%20data%20source)%20of%20the%20pbix%20report%20for%20which%20the%20refresh%20failed.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20the%20screenshot%20below%2C%20%E2%80%9Cabc%E2%80%9D%20account%20is%20used%20to%20connect%20to%20the%20data%20source%20for%20my%20report.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%224.png%22%20style%3D%22width%3A%20568px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294959i5E841848E8D9E604%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%224.png%22%20alt%3D%224.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EStep%202%3C%2FU%3E%3C%2FSTRONG%3E%3A%26nbsp%3B%20Note%20down%20the%20service%20account%20used%20by%20PBIRS%20from%20%E2%80%9CService%20Account%E2%80%9D%20section%20of%20Report%20Server%20Configuration%20manager%20of%20PBIRS%3C%2FP%3E%0A%3CP%3EFrom%20the%20screenshot%20below%2C%20%E2%80%9CVirtual%20Service%20account%E2%80%9D%20is%20my%20PBIRS%20service%20account%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%225.png%22%20style%3D%22width%3A%20522px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294960i290598A733709941%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225.png%22%20alt%3D%225.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EStep%203%3C%2FU%3E%3A%3C%2FSTRONG%3E%20The%20account%20used%20to%20connect%20to%20the%20data%20source%20in%20pbix%20report%20(%E2%80%9Cabc%E2%80%9D%20account%20obtained%20from%20step%201)%20needs%20to%20be%20provided%20permissions%20mentioned%20in%20the%20next%20step%20on%20the%20folder%20security%20of%20the%20%E2%80%9CTemp%E2%80%9D%20folder%20associated%20with%20the%20PBIRS%20service%20account%20(%E2%80%9CVirtual%20Service%20account%E2%80%9D%20obtained%20from%20step%202).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20you%20have%20a%20%3CSTRONG%3Ebuilt-in-account%3C%2FSTRONG%3E%20configured%20as%20the%20service%20account%20for%20PBIRS%20(such%20as%20Virtual%20Service%2C%20Network%20Service%2C%20Local%20System)%2C%20you%20should%20look%20for%20Temp%20folder%20in%20the%20following%20location%20on%20PBIRS%20Server%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3EC%3A%5CWindows%5CServiceProfiles%5CPowerBIReportServer%5CAppData%5CLocal%5CTemp%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20you%20have%20a%20%3CSTRONG%3Edomain%20account%20%3C%2FSTRONG%3Econfigured%20as%20the%20service%20account%20for%20PBIRS%2C%20you%20should%20look%20for%20Temp%20folder%20in%20the%20following%20location%20on%20PBIRS%20Server%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3EC%3A%5CWindows%5CUsers%5C%3CDOMAIN%20account%3D%22%22%3E%20%5CAppData%5CLocal%5CTemp%3C%2FDOMAIN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EStep%204%3C%2FU%3E%3A%20%3C%2FSTRONG%3E%3CSTRONG%3E%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3EProvide%20the%20following%20permissions%20to%20the%20data%20source%20credential%20account%20%E2%80%9Cabc%E2%80%9D%20in%20the%20folder%20security%20of%20PBIRS%20service%20account%E2%80%99s%20(Virtual%20service%20account)%20temp%20folder%20as%20shown%20below%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERead%20%26amp%3B%20Execute%3C%2FLI%3E%0A%3CLI%3EList%20folder%20contents%3C%2FLI%3E%0A%3CLI%3ERead%3C%2FLI%3E%0A%3CLI%3EWrite%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%226.jpg%22%20style%3D%22width%3A%20518px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294961iA398E7FF40094463%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%226.jpg%22%20alt%3D%226.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20implementing%20the%20steps%20mentioned%20above%2C%20you%20should%20see%20the%20refresh%20working%20successfully%20for%20the%20pbix%20report.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CU%3EWhy%3C%2FU%3E%3C%2FSTRONG%3E%3CSTRONG%3E%3CU%3E%20is%20providing%20permission%20to%20PBIRS%20Service%20account%E2%80%99s%20Temp%20folder%20for%20the%20data%20source%20account%2C%20required%3F%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20performing%20scheduled%20refresh%20for%20reports%20that%20use%20Access%20DB%20as%20the%20data%20source%2C%20the%20Mash%20up%20process%20performs%20impersonation%20using%20the%20account%20provided%20in%20the%20data%20source%20credentials%20section%20and%20hence%20checks%20the%20PBIRS%20Service%20account%E2%80%99s%20folder%20security%20to%20validate%20if%20the%20data%20source%20credential%20account%20used%20in%20the%20report%20has%20the%20necessary%20privileges.%20This%20validation%20decides%20the%20success%2Ffailure%20of%20the%20report%20refresh.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20is%20a%20screenshot%20from%20%3CSTRONG%3EProcess%20Monitor%3C%2FSTRONG%3E%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsysinternals%2Fdownloads%2Fprocmon%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EProcess%20Monitor%20-%20Windows%20Sysinternals%20%7C%20Microsoft%20Docs%3C%2FA%3E)%20trace%20that%20explains%20the%20behaviour%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%227.png%22%20style%3D%22width%3A%20813px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294962iD1FE9304A5D1CE71%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%227.png%22%20alt%3D%227.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20this%20article%20helps%20you%20in%20fixing%20your%20report%20refresh%20issue!%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAuthor%3A%26nbsp%3B%3C%2FSTRONG%3E%20Uma%20S%20M%20%E2%80%93%20Technical%20Advisor%2C%20SQL%20Server%20BI%20team%2C%20Microsoft%3CBR%20%2F%3E%3CSTRONG%3EReviewer%3A%26nbsp%3B%3C%2FSTRONG%3EKane%20Conway%20%E2%80%93%20Escalation%20Engineer%2C%20SQL%20Server%20BI%20team%2C%20Microsoft%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2537178%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPBIRS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epbix%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Essrs%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jul 12 2021 12:37 AM