Excel PowerQuery 使用的 WebAPI OData 服务的 WAAD 身份验证 [英] WAAD Authentication with WebAPI OData service consumed by Excel PowerQuery

问题描述

我创建了一个带有 OWIN 中间件的 WebAPI OData 3.0 Web 服务，该中间件配置为使用 Windows Azure Active Directory 进行身份验证.ODataController 标有 [Authorize] 属性，IAppBuilder 配置如下:

I've created a WebAPI OData 3.0 web service with an OWIN middleware, which is configured for authentication with Windows Azure Active Directory. The ODataControllers are marked with an [Authorize] attribute, and the IAppBuilder is configured as follows:

app.UseWindowsAzureActiveDirectoryBearerAuthentication(
            new WindowsAzureActiveDirectoryBearerAuthenticationOptions
            {
                Tenant = ConfigurationManager.AppSettings["ida:Tenant"],
                TokenValidationParameters = new TokenValidationParameters {
                    ValidAudience = ConfigurationManager.AppSettings["ida:Audience"]
                },
            });

ida:Tenant 是我的 Windows Azure 租户，ida:Audience 是 App ID Uri.

ida:Tenant is my Windows Azure tenancy, and ida:Audience is the App ID Uri.

现在我想使用 Excel PowerQuery 使用此服务，使用 AzureAD 中的帐户进行身份验证.但是，当我选择组织帐户"并尝试登录"时，我收到以下错误:

Now I would like to consume this service using Excel PowerQuery, authenticating using an account from the AzureAD. However, when I choose "Organizational account" and try to "Sign in", I get the following error:

无法连接.此资源不支持此凭据类型.

Unable to connect. This credential type is not supported for this resource.

在 Fiddler 中我可以看到请求是使用 Bearer 标头发出的，但它是空的.

In Fiddler I can see that the request is made with a Bearer header, but it is empty.

我想实现类似于查询 AzureAD Graph 时的行为.
例如，如果我尝试使用 https://graph.windows.net/.onmicrosoft.com/users?api-version=2013-04-05，打开一个单点登录窗口，在 Fiddler 中我可以看到传递了一个令牌.

I would like to achieve a behavior similar to when querying AzureAD Graph.
For example, if I try to consume https://graph.windows.net/.onmicrosoft.com/users?api-version=2013-04-05, a single sign-on window opens, and in Fiddler I can see that a token is passed.

我怎样才能实现这种行为?我错过了什么?

How can I achieve this behavior? what am I missing?

谢谢！

推荐答案

这是身份验证期间 PowerQuery 和 OData 服务之间的预期流程:

Here is the expected flow between PowerQuery and an OData service during authentication:

• 当您在构建器中输入服务的 URI 时，单击确定，您将收到一个凭据提示，要求您提供凭据以访问该服务.
• 通常，如果 Azure Active Directory (AAD) 是您的身份提供者，您会选择组织帐户.

• 当您单击登录时，PowerQuery 将向您的服务发送质询请求，这是您看到的空承载.原因是，我们不知道您的身份提供者是什么，或者我们应该在哪里登录，该请求需要一个带有 WWW-Authenticate 标头的 401/403 响应，该标头具有身份验证端点 url.

• When you enter the URI to your service in the builder, click ok, you will get a credential prompt asking for your credentials to access the service.
• Typically, you would choose Organizational Account if Azure Active Directory (AAD) is your Identity Provider.

• When you click sign in, PowerQuery will send a challenge request to your service, which is the empty bearer you are seeing. The reason is, we don't know what's your identity provider or where should we log you in, the request is expecting a 401/403 response with a WWW-Authenticate header that has the authentication endpoint url.

这是预期的标头格式:WWW-Authenticate authorization_uri="token service uri" 引号是可选的.如果我们没有找到该标头，您会收到错误消息无法连接.不支持此凭据类型.

Here is the expected header format:WWW-Authenticate authorization_uri="token service uri" quotes are optional. If we don't find that header, you get the error message 'Unable to connect. This credential type is not supported'.

在 AAD 中，您的应用程序对象有两件重要的事情来完成这项工作:

There are two important things regarding your application object in AAD to make this work:

• AppIdUris 属性必须具有与您的服务 URI 匹配的通配符 URI.当我们发送登录请求时，我们必须包含一个资源 ID，该资源是我们正在连接的服务的权限.因此，如果您的服务 url 是:myservice.com/myODatafeed.svc，权限包括方案、主机和端口号，myservice.com/将是权限.对于可能具有不同租户的服务，例如:company1.myservice.com，AppIdUri 必须具有 https://*.myservice.com.否则，只需 https://myservice.com.
• 第二件事(这是特定于 AAD 的)，AAD 目前不支持第一方客户端 (PowerQuery) 到第三方服务(您的服务)的身份验证.但希望很快 :) 也许就在你完成其余工作的时候 :)！

这篇关于Excel PowerQuery 使用的 WebAPI OData 服务的 WAAD 身份验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！