Web API data source refresh issue

[SowjanyaK]

Hi Team,

I have created a web api which validates the http header and gives the response in Json format. I have added this api as a data source for a report in power bi desktop which is working fine. But when i publish the report to powerbi.com it fails the data refresh with an exception "Invalid Credentials" and the configured header value is not available in the data connection. need help in fixing this issue

Thanks 

Sowjanya

[v-qiuyu-msft]

Hi @SowjanyaK,

After you publish the report to Power BI service, please go to the Dataset Settings page. If the Power Query to get data contains web.page function, you will need a gateway for the dataset. You can configure a on-premise data gateway and create a web data source with the same URL used in desktop. Then set the dataset use this data gateway. 

Best Regards,
Qiuyun Yu 

[SowjanyaK]

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

[SowjanyaK]

Hi @v-qiuyu-msft

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

[SowjanyaK]

Hi @v-qiuyu-msft,

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

[v-qiuyu-msft]

Hi @SowjanyaK,

I can reproduce the issue on my side. And I already reported this issue internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[danilm]

Do you have any update? I have the same issue - Web API authentication works in the desktop, but fails auto-refresh when published.

I am using Json.Document(Web.Contents("...",  [ApiKeyName="Api-Token")) to retrieve data from the REST API call, Cloud Power BI connection authentication does not even provide the "Web API" option.

Writing authentication key value directly into Web.Content (authentication header) has the same problem. Works in Power BI desktop, but fails refreshes when published into the cloud.

Please let me know if this is a known issue, and if so, any timeline for the fix?

Thank you!

[SowjanyaK]

 This is the response that i got from Power BI team, 

I don’t think optional records are supported in PowerBI service yet. Is there a way to put all the info inside the url instead of composing it using []?

Need to raise a support ticket with PowerBI team to get the dedicated support,

[danilm]

Hi @SowjanyaK,

I moved the authentication token inside the query string, as you suggested.

Changed

Json.Document(Web.Contents("...",  [ApiKeyName="Api-Token"))

to

Json.Document(Web.Contents("...&Api-Token=xxxxxxxxxx"))

Same problem. Works in PowerBI Desktop, but fails in cloud (Authentication Method: Anonymous):

Failed to update data source credentials: The credentials provided for the Web source are invalid.

Can you confirm this is a known issue, or am I doing something wrong?

Thank you,

Danil

[SowjanyaK]

I think then this is an issue with power bi, you can raise a support request with power bi team. 

[cecyarmida]

Hi @SowjanyaK,

I ran into the same issue and was able to bypass the problem using Azure Fuctions Proxy, here is a blog I wrote about that issue:

https://blog.kloud.com.au/2018/07/23/use-anonymous-authentication-on-web-data-sources-with-a-api-key...

I hope it helps