c# - Power Query/PowerBI连接到通过AAD保护的自定义oDATA提要

Question

我已经使用ASP.NET Web API创建了自定义OData提要。使用Azure Active Directory进行身份验证配置此服务。我当前遇到的问题是尝试将Power BI或Power Query连接到OData feed时。输入凭据后，出现以下错误：


Invalid_resource：AADSTS50001：在名为XXX.onmicrosoft.com的租户中找不到名为https://localhost:44320的应用程序。如果租户的管理员未安装该应用程序，或者该租户中的任何用户未同意该应用程序，则可能发生这种情况。您可能已将身份验证请求发送给错误的租户。


我很确定我已经正确配置了AAD，因为我可以通过Web浏览器正常连接。我不确定是否有可能连接Power Query，因为我在各种论坛中看到了相互矛盾的帖子！

任何帮助将不胜感激。

Answer 1

在尝试使用PowerQuery将API数据获取到Excel电子表格中时，我也遇到了此错误，并且很难克服它，主要是因为有关这方面的文档太少了。

问题-如何从Azure中的API导入数据到Excel电子表格？

我有一个运行在Azure中的ASP.NET API，并在我自己的域中通过URL公开，例如https://api.myapp.net（而不是内置的Azure网址）。该API通过OWIN中间件UseWindowsAzureActiveDirectoryBearerAuthentication连接到Azure AD，即Azure中的App Service已关闭AAD身份验证。该应用程序已在AAD中注册为多租户，与托管应用程序服务资源的租户不在同一租户中。

问题1-凭证

因此，首先在Excel中执行数据>从Web> https://api.myapp.net/Products，然后选择“组织帐户”，然后单击“登录”，出现此错误：



有两个不同的修复程序：

1.在所有401上返回WWW-Authenticate响应标头

如果您使用owin中间件在代码中启用了API的AAD，则需要确保服务在401响应中返回正确的WWW-Authenticate标头给客户端，特别是我们必须指定AAD登录端点作为授权uri，例如：

WWW-Authenticate: Bearer realm="", 
authorization_uri="https://login.microsoftonline.com/<<tenant id of your users>>"

参见： this TechNet question which suggested this solution

2.在Azure门户中打开API应用程序服务的AAD身份验证


或者，在Azure门户中托管应用服务本身的租户
转到应用服务并找到API应用服务
在“身份验证/授权”中，打开“应用服务身份验证”
对于“未经身份验证时采取的措施”，选择“使用Azure Active Directory登录”
在“身份验证提供程序”下，单击“ Azure Active Directory”，然后选择“高级”设置
在“客户ID”下，输入用于API应用注册的应用ID
在“发行人网址”下，输入API用户源自的租户的登录端点
在“允许的令牌受众”下，确保您已添加API的实际网址，例如 https://api.myapp.net
保存更改


本质上，此配置描述为 here

问题2-应用注册

现在，当您单击查询上的登录时，返回Excel，将弹出一个弹出窗口，并将您带到您配置的租户的Microsoft登录页面。输入凭据并登录后，您可能会收到此错误（问题中的那个）：



要解决此问题，您需要确保已在AAD中正确注册了该应用程序。

这是...


在用于您的应用程序注册的租户的Azure门户中
转到Azure Active Directory>应用程序注册并找到API服务的注册
编辑清单，并确保在identifierUris列表中配置了实际部署的API URL，例如 https://api.myapp.net（已经配置了Azure内置URL）

{
  "identifierUris": [
    "https://api.myapp.net",
    "https://<mytenant>.onmicrosoft.com/<myappregname>"
  ]
}

如果应用程序是多租户，则需要确保此URL中使用的域为 verified with Azure
您还必须确保 user_impersonation范围可用于该应用程序：

{
  "oauth2Permissions": [
    {
      "adminConsentDescription": "Allow the application to access myapp on behalf of the signed-in user.",
      "adminConsentDisplayName": "Access myapp",
      "id": "xxxxx-xxx-xxx-xxx-xxxxxxx",
      "isEnabled": true,
      "lang": null,
      "origin": "Application",
      "type": "User",
      "userConsentDescription": "Allow the application to access my on your behalf.",
      "userConsentDisplayName": "Access my app",
      "value": "user_impersonation"
    }
  ]
}

保存更改。


问题3-允许令牌受众

现在回到Excel中，您应该可以通过登录，但是在单击“连接”时，您可能会遇到此错误：



现在在提琴手中查看，您将看到AAD登录有效并返回令牌，但是当将其发送到API时，您将获得401。

仅当您通过代码而不是通过Azure门户启用AAD时，这才是问题（请参见上面的问题1！）。要解决此问题，您需要确保传递给owin中间件的 TokenValidationParameters class已将 ValidAudience设置为已部署API的实际URL。

运行查询

有了所有这些设置，现在一切都可以正常工作了，回到Excel中...


单击登录，将弹出一个弹出窗口，并带您进入您配置的租户的Microsoft登录页面，并使用您的凭据登录
点击连接
PowerQuery编辑器随后将打开并显示从API检索到的数据
单击主页>高级编辑器，您将能够查看原始查询-这是PowerQuery使用的查询语言 ​M-query syntax，在我的情况下，数据为平面数组，因此可以满足以下条件：

let
    Source = Json.Document(Web.Contents("https://api.myapp.net/Products")),
    #"Converted to Table" = Table.FromRecords(Source)
in
    #"Converted to Table"

单击“关闭并加载”以将数据返回到Excel工作表。


如何运作

如果您关心（并且仍在阅读本文！），则此方法的工作方式似乎是：


PowerQuery请求在“ Microsoft PowerQuery For Excel”内置应用程序下访问您的API（客户端ID a672d62c-fc7b-4e81-a576-e60dc46e951d）
登录时，AAD将API上的动态范围 user_impersonation（由资源URL https://api.myapp.net标识）授予“ Microsoft PowerQuery For Excel”应用程序
通过转到企业应用程序，检查Microsoft应用程序并搜索Microsoft PowerQuery For Excel，可以在门户中看到此内容。






很长的帖子很抱歉，但是希望这可以帮助某人做一些看似微不足道的事情-将Azure中API的数据提取到Excel中！