WAAD Authentication with WebAPI OData service consumed by Excel PowerQuery

Question

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 is my Windows Azure tenancy, and ida:Audience is the App ID Uri.

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.

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

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?

Thanks!

Hadeel Elbitar Hadeel Elbitar · Accepted Answer · 2015-02-04T20:08:40

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

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.
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'.
For example, in your scenario, the token service uri is https://login.windows.net
When we receive that response, we will get the url from the header and issue a login request, at which point you will see the login page from AAD and you will be able to login using your organizational credentials.
We will wait for the sign in result, which should be a token, that token will be used to fill in the bearer in the header at anytime you request data from that service.

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

The AppIdUris property has to have a wildcard URI that would match with your service URI. When we send the login request we have to include a resource id, the resource is the authority of the service we are connecting to. So if your service url is: myservice.com/myODatafeed.svc, the authority includes the scheme, the host and the port number, myservice.com/ would be the authority. For services that might have different tenants for example: company1.myservice.com, the AppIdUri has to have https://*.myservice.com. Otherwise, just https://myservice.com.
The second thing (and this on is AAD specific), AAD doesn't support first party client (PowerQuery) to third party service (your service) authentication today. but hopefully soon enough :) Maybe just when you get the rest done :)!

WAAD Authentication with WebAPI OData service consumed by Excel PowerQuery

2 Answers