cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
david_piepgrass
Regular Visitor

Does the "OData feed" connector support "Web API" authentication?

The OData connector's Web API mode always says "A web api key can only be specified when a web api key name is provided." Is this broken? Or is there a way to make it do something?

david_piepgrass_0-1638117466856.png

I hacked up some code in our server to find out what headers are sent by PowerBI; looks like "Web API" mode doesn't even send a request. Since our normal Azure Active Directory authentication is not supported, it looks like realistically the only mode we can use is Basic (though I know we could also use a custom connector); For future reference, the headers I receive from PowerBI in Basic mode is:

 

Headers on /odata/entities?$top=1000:
Accept = application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding = gzip, deflate
Authorization = Basic dXNlcm5hbWU6cGFzc3dvcmQ=
Host = localhost:5001
User-Agent = Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion = 3.0
OData-MaxVersion = 4.0

 

 where the username and password are base64-encoded after "Authorization = Basic".

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @david_piepgrass ,

You can pass ApiKeyName in the OData.feed() use Power Query. For more information, see:


authenticating with Excel Power Query against .Net Odata Web Api
API Strategies with Power BI

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @david_piepgrass ,

You can pass ApiKeyName in the OData.feed() use Power Query. For more information, see:


authenticating with Excel Power Query against .Net Odata Web Api
API Strategies with Power BI

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

So, fiddling around, the answer seems to be that the "OData feed" connector does not support "Web API" authentication, but there's a workaround involving the M function called OData.Feed:

  1. Instead of Get Data | OData feed, click Get Data | Blank query
  2. In the query line (beside "fx") write a query of the form `= OData.Feed("https://your_domain/odata", null, [ApiKeyName = "YourCustomKeyName"])` and press Enter to run it
  3. A little yellow bar should appear that says "Please specify how to connect." with an "Edit Credentials" button. Click the button. Now the "Web API" option will work (Note: Power BI caches credentials and will not ask for credentials again if it has successfully logged in earlier)
  4. Instead of a navigation tree, you get a table with three columns. The first column is "Name" and has a list of OData table names. The second column is "Data" and each cell says "Table". For each table you want to use, right-click the word "Table" and choose "Add as New Query" from the menu. This will create a new query to the list on the left. Unlike normal OData feeds, tables created this way will have a "_table" suffix.
  5. Close the window. Choose "Yes" when asked to "apply your changes". Your tables should appear in the Fields list on the right, as usual.

But wait! What does the "Web API" option actually do? Apparently all it does is add a key-value pair to the URL (twice?). Here is the relative URL and headers that my app received from Power BI when I set the "Key" to "YourWebApiKey":

Headers on /odata/entities?$top=1000&YourCustomKeyName=YourWebApiKey&YourCustomKeyName=YourWebApiKey:

Accept = application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding = gzip, deflate
Host = localhost:5001
User-Agent = Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion = 3.0
OData-MaxVersion = 4.0

Lucien, the domain of the second link (angryanalyticsblog.azurewebsites.net) is currently inaccessible.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!