authenticating with Excel Power Query against .Net Odata Web Api

Question

I am trying to use Power Query to download an Odata Feed that I created using .net Web Api 2 and the OData v4 nuget package.

I'm trying to access an Odata feed that requires authentication. When I edit the authentication type in power query, I'm never seeing the authentication key come through in the request.

How do you configure Power Query to use a specific type of authentication?

Bonus: I'm using OAuth, so how would I configure power query to send in a header with auth data that includes "Authorization Bearer:token_here"

This seems to be related. Maybe I need to configure my web api to respond with "WWW-Authenticate" header when authorization fails? — TWilly, Oct 19 '15 at 23:28

Carl Walsh · Accepted Answer · 2015-10-21T01:50:38.180

6

Web API credentials are for putting a secret value into to the URL query (i.e. your API key for some website).

There's currently no way to add your own Bearer token in Power Query from the credential dialog.

It's less secure and can't be refreshed, but you can hardcode your credential directly using OData.Feed's Header parameter:

= OData.Feed("http://localhost/", null, [Headers = [Authorization = "Bearer token_here" ] ])

(Alternatively, it might be easier to configure your server to accept Basic auth, which is supported in Power Query.)

edited Oct 21 '15 at 01:50

answered Oct 20 '15 at 15:52

Carl Walsh

6,100
2
46
50

When I send in a test request with the API key filled in, I'm not seeing the key come through in either the header or the url parameters. Is there something I need to configure on the .net side to get excel to send in the api key? – TWilly Oct 20 '15 at 21:04
3

Here's an example of ApiKeyName: `= OData.Feed("http://example.com/Path?Version=22", null, [ApiKeyName = "MyCustomKeyName"])` Then set credentials to Web API with value "SuperSecretKey" Power Query makes the request for `http://example.com/Path?Version=22&MyCustomKeyName=SuperSecretKey` – Carl Walsh Oct 21 '15 at 01:54
Thanks so much. Is there any documentation you could point me to that would have this type of information in case I run into any additional issues? – TWilly Oct 21 '15 at 14:08
1

Some documentaton about odata.feed : https://msdn.microsoft.com/en-us/library/mt260868.aspx – Haroon May 25 '16 at 06:23
This is not correct approach since token has ip address inside it and can become suspicious by your backend service. – Hrvoje Matic May 26 '17 at 20:41

score 6 · Answer 2 · answered May 26 '17 at 20:43

This is how it should be done and this is tested and working. Token is always regenerated.

let
GetJson = Json.Document(Web.Contents("https://myservice.azurewebsites.net/oauth/token",
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("login=MYUSERNAME&password=MYPASSWORD&grant_type=password")
     ])),
    access_token = GetJson[access_token],
    AccessTokenHeader = "Bearer " & access_token,
JsonTable =  Json.Document(Web.Contents(

  "https://myservice.azurewebsites.net/odata/Cities",
  [
   Query=[ #"filter"="", #"orderBy"=""],
   Headers=[#"Authorization" = AccessTokenHeader ]
  ])),
#"Cities" = Table.FromRecords(JsonTable[value])
in
    #"Cities"

// Note, when setting privacy credential, set it to "Organizational", and not private and surely not public.

for MYUSERNAME / MYPASSWORD - are these credentials for a service account you've created in your Azure AD? — bkwdesign, Apr 09 '19 at 20:14

authenticating with Excel Power Query against .Net Odata Web Api

2 Answers2

Linked