topic Re: PBIRS rest api, data to Power BI report, authentication in Report Server

PBIRS rest api, data to Power BI report, authentication

SorenHElisa — Mon, 29 May 2023 13:17:21 GMT

Hi,

Tried finding something about this issue, but couldn't. Whether someone knows if this has already been answered, please point me in the rights direction.

Environment: Power BI Report Server Version1.16.8420.13742(January 2023)

What I'm trying to achieve:

make a Power BI report listing all the data sources to all Power BI reports in the server, data fetched through https://<server>/reports/api/v2.0 using OData connector

Purpose:

check if any reports are using an old data source connection,

What's working: Getting the complete list of reports from the API through PowerBIReports, after filtering out "My Reports" folders.

What's not working: expanding Data Sources for the reports

Error message in Power BI Desktop:

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (An error has occurred.)

Error message in Power BI Report Server:

ERROR|223|library|Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '<my user account> are insufficient for performing this operation.;

Checked:

I have put myself as System administrator in the Power BI Report Server security

I'm also a sysadmin in the SQL server.

Where can I give myself more rights so I can get this working? Are there some stored procedures that are not granted to my account that's needed for these kind of operations?

Kind regards,

Sören

Re: PBIRS rest api, data to Power BI report, authentication

josef78 — Mon, 29 May 2023 15:47:06 GMT

Check also if you are local admin on server where is runnig Power BI Report Server

Re: PBIRS rest api, data to Power BI report, authentication

SorenHElisa — Tue, 30 May 2023 05:11:46 GMT

Good point. Thanks josef78 !

Tested, but did not change anything. I'll have our Management Services reboot the server tonight if that's the problem. Currently Tuesday morning, so will take a while before I can test again.

Will post updates here when news are available.

Re: PBIRS rest api, data to Power BI report, authentication

SorenHElisa — Tue, 30 May 2023 06:43:28 GMT

Did not need to wait for the server to reboot. Found the problem.

After some deeper digging I found reports that were "shut out" of admin persons, i.e. the BUILTIN\Administrators had been removed from the security page for the reports. After adding BUILTIN\Administrators with Content Manager rights where it was missing, the datasource report is now refreshing fine.

Here's a query that helped me out:

with a as ( select PolicyID from [ReportServer].[dbo].[PolicyUserRole] (nolock) where UserID = '<GUID>' --get the ID from table 'Users' ) select * from Catalog c (nolock) where not exists ( select 1 from a where a.PolicyID = c.PolicyID ) and path not like '%Users Folders%'

Have a nice day!