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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors