Refreshing a Dynamic Web Query in PBIRS

[Anonymous]

Hi guys,

Scenario:

I'm trying to build a report that queries the same REST API that lives on a number of servers. This list of servers is stored in a SQL database.

Problem:

I've built the Power Query, and it works fine. The issue is when publishing the report on Report Server, I get a "Credentials are required [...]" error message.

So my question is, where can I enter the credentials (in this case anonymous is good enough)?

• When refreshing from PowerBI Desktop RS, the credentials are asked for when refreshing and it then works
• When refreshing from PowerBIRS, the following error message is logged: 

  0] -1055784934: Credentials are required to connect to the Web source. (Source at https://server1:8080/Service/api/). The exception was raised by the IDataReader interface.

But there's nowhere to enter the credentials for each of the service dynamically hit by the report - the only DataSource known to ReportServer is the SQL Server.

Is it possible to hardcode it in PowerQuery?

Details

This is a simplified version of my Power Queries:

Get-Health function:

(Server) =>
let
    Source = Json.Document(Web.Contents("https://" & Server & ":8080/Service/api/"))
in
    Source

Health query:

let
    Source = Sql.Database("DBServer", "DB", [Query="SELECT ServerName FROM Servers"]),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Health", each #"Get-Health"([ServerName])),
    #"Expanded Health" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Health", {"timeToLive", "data"}, {"Health.timeToLive", "Health.data"}),
    #"Expanded Health.data" = Table.ExpandListColumn(#"Expanded Health", "Health.data"),
    #"Expanded Health.data1" = Table.ExpandRecordColumn(#"Expanded Health.data", "Health.data", {"isHealthy", "lastSuccessfulHealthCheck", "name", "state", "version"}, {"Health.data.isHealthy", "Health.data.lastSuccessfulHealthCheck", "Health.data.name", "Health.data.state", "Health.data.version"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Health.data1",{"Health.timeToLive"})
in
    #"Removed Columns"

[v-qiuyu-msft]

Hi @Anonymous,

1. Regarding web.contents() function, you can follow this blog to set credential: https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

2. Also when we get data use web data source in Power BI desktop, we can specify Authorization value: 

3. After publish the report to Power BI report server, please go to Data Source tab of the report to set corresponding credential. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi Qiuyun,

Unfortunately, I've already tried these. My issue actually not that I want to set credentials, but that I want to make PBIRS understand that it can use anonymous logon.

To answer your points

1. I've tried that, but because there's no login and password necessary for these endpoints, it doesn't work. I've tried forcing a header with no authorization attribute, with no luck
2. As I mentionned in my post, the calls to the webservices are dynamically generated. The list of endpoints is retrieved from SQL and PowerQuery queries one after the other using a funciton. Therefore I don't have the option to use the UI to configure the headers. That being said, by manually configuring them, it doesn't work because what I need is to convince PBI RS to use anonymous.
3. Yes, that would work if the endpoints I call were a finite list, but because they are dynamically generated, they don't appear here.

Note that functions are not supported by the PBI Service - probably because it makes credentials complicated in the case a login/password is needed by each.

So I suppose it might be the same with PBI RS, except that there's nothing that explicitely says so. 

But if it defaulted to Anonymous, for endpoints that don't require authentication like mine, at least it would work for this case.

[Grumelo]

You can pass Header Parameters in a Web.Contents() call

Example:

= Json.Document(Web.Contents("<URL>", [Headers=[Accept="application/json"]]))

I have the same issue as you with my dynamic queries.

I've not tried yet to pass the Authorization in the Header. I'll let you know if it works.

In Power BI Desktop you can define the permission as Global Permissions in the Data Source Setting but not on PBIRS...

[mpontrelli]

Hello,

Did you ever get your web queries working with PBIRS.  I have them working fine in my reports, they get the token, connect, and retrieve the data no problem, however when I load them up to PBIRS, it says I do not have access to the data, and I do not see any option to add a web data source.

Any help would be appreciated.

Thanks

Mike