I have an OData feed that I want to use with Excel / PowerPivot. The channel is secured using OAuth 2 (Windows Azure Active Directory is an auth server). In the advanced tab of the PowerPivot table import wizard, there is some user interface that allows you to specify OAuth and allow you to enter an authorization token (I assume this is an access token, but I could be wrong):
However, when I test the connection, I get an error message that tells me that I have to enter a username and password if I do not use Windows authentication. This violates the purpose of OAuth 2, but as an experiment, I entered a username and password. This time, when I test the connection, I received an Uniredhorized (401) error from the server.
Using the script to view the request that was issued by the test connection, I see that the authorization header was not included in the request, so, obviously, it will never work. I would hope / expect that the authorization token will be included as a carrier token in the authorization header. This is what my service requires.
I also tried adding the refresh and isfresh token token, but the result was the same. It is possible to use Client Secret, but I have not tried this because it would not be safe (I think OAuth 2 only supports confidential clients that do not have Excel).
So my question is: could anyone use this OAuth parameter to import data into Excel or PowerPivot? If so, how do you do it?
Update: I realized that my service does not respond with the correct WWW-Authenticate header values, as indicated here:
http://self-issued.info/docs/draft-ietf-oauth-v2-bearer.html#authn-header
So, I changed the service code. Now I see the correct WWW-Authenticate response header (scheme = Bearer, realm = my realm), but that does not matter. I do not see any changes in the queries issued by Excel.
Update: I also tried using the OAuth version 1 of the WW-Authenticate header (schema = OAuth), but still nothing.