Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
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"
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
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
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.
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...
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
6 | |
4 | |
3 | |
2 |
User | Count |
---|---|
13 | |
10 | |
5 | |
3 | |
3 |