Importing OData feed into PowerPivot where the feed is OAuth 2 protected

Question

I have an OData feed that I want to consume using Excel/PowerPivot. The feed is protected using OAuth 2 (Windows Azure Active Directory is the auth server). The advanced tab on the PowerPivot table import wizard has some UI that allows you to specify OAuth and lets you put in an authorization token (I'm assuming this is an access token, but I could be wrong):

However, when I do test connection I get an error that tells me I have to put in a username and password if I'm not using Windows Authentication. This defeats the objective of OAuth 2, but as an experiment, I did put a username and password in. This time, when I test the connection I got an Unauthorized (401) error from the server.

Using fiddler to look at the request that was issued by the test connection I can see that no Authorization header was included in the request, so obviously it was never going to work. I would have hoped/expected that the authorization token would be included as a Bearer token in the Authorization header. This is what my service requires.

I also tried putting in a refresh token and arefresh token URL but the result was the same. There is an option to use a Client Secret but I haven't tried this because that would not be secure (I think OAuth 2 only supports that for confidential clients, which Excel is not).

So, my question is: Has anyone been able to use this OAuth option to import data in to Excel or PowerPivot? If so, how do you do it?

Update: I realised my service was not responding with the correct WWW-Authenticate header values as specified here:

http://self-issued.info/docs/draft-ietf-oauth-v2-bearer.html#authn-header

So I changed the service code. Now I see the the correct WWW-Authenticate response header (scheme=Bearer, realm=my realm), but it makes no difference. I don't see any change in the requests issued by Excel.

Update: I also tried using the OAuth 1 version of the WW-Authenticate header (scheme=OAuth) but still nothing.

Answer 1

I registered this with Microsoft:

http://connect.microsoft.com/SQLServer/feedback/details/802179/unable-to-import-data-from-odata-feed-protected-by-oauth-2

It turns out that this is a bug in PowerPivot. The decision from Microsoft is that they will fix the problem in PowerQuery rather than PowerPivot. At the moment, PowerQuery has the same limitation as PowerPivot.