时间戳

首页 > 解决方案 > Why does Power Query call Azure API Management backend URL?

azure - Why does Power Query call Azure API Management backend URL?

问题描述

I have an Azure App Service hosting an OData endpoint that is behind an Azure API Management (APIM) instance. To prevent calling the App Service directly it is protected by a certificate that only the APIM has.

When I call the APIM URL through Chrome or Postman, it behaves as expected. Just one request with no redirects or funny business, and it returns the OData root. Here is a Fiddler log of a request to the APIM using Postman

However, when using the same URL as an OData source in Power Query using OData.Feed(), it returns a 301 which forwards to the backend URL, which obviously fails because that URL is protected by a certificate. Here is a Fiddler log of a request to the APIM using Power Query in Excel

I've configured the subscription key to be passed in the headers, but I've also tried it as a query param and it doesn't work in Power Query either way. I've also tried using an OData entity endpoint directly (to avoid the $metadata call) with no luck.

The user agent Power Query uses is Microsoft.Data.Mashup, but I haven't found any documentation about its compatibility with APIM, but that shouldn't matter, right?

标签： azureazure-web-app-serviceodatapowerqueryazure-api-management

解决方案

在研究了两天之后，我以典型的方式在 StackOverflow 上发布后立即找到了答案。如果有人有同样的问题，我会留下这个问题。

问题是 Power Query 连接器自动跟随 @odata.context 链接获取元数据，以及 @odata.nextLink 链接进行分页。这些链接仍然将应用服务站点作为主机而不是 APIM 主机。

因此，快速编辑 APIM 中的出站规则能够解决问题

<outbound>
    <base />
    <set-variable name="backendBaseUrl" value="@(context.Request.OriginalUrl.Scheme + "://" + context.Request.OriginalUrl.Host.ToString() + context.Api.Path)" />
    <find-and-replace from="@("http://" + context.Request.Url.Host.ToString())" to="@((string)context.Variables["backendBaseUrl"])" />
    <find-and-replace from="@("https://" + context.Request.Url.Host.ToString())" to="@((string)context.Variables["backendBaseUrl"])" />
</outbound>

在这里，我必须遵守规则来替换 http 和 https URL，以防某些配置发生变化。

推荐阅读