Azure OData Feed Web API Authentication Setup With Azure AD

Objective

One of the most common scenarios you might come across is to build and deploy a Web API in Azure and ensure that the Web API is authenticated utilizing the Azure AD credentials.

I too had a similar requirement and to add more on top, my requirement was to develop an API which could return data in the OData format so that the API could be consumed from the Excel Power Query but at the same time the API needed to be secure with Azure AD Authentication. The requirement was extremely logical since the end users could use the Excel Power query to connect to an API in Azure which returned data in the OData format using their Organizational Account and let them play around with the data in Excel.

My intention was to achieve this with a No-Code solution since the Azure API Apps already provided the capability to set the authentication mechanism with the Azure AD and also, the Excel Power Query supported the option to access an API with the help of an Organizational Account.

The screenshot above shows how an Azure API App is set to be authenticated with Azure Active Directory.

The screenshot above shows the way the Odata-Feed connectivity option in Power Query provides the capability to authenticate utilizing Azure AD accounts.

Problem Statement

Irrespective of the fact that the Objective is simple, it was extremely difficult to get this done because it is not yet documented in detail on how this could be achieved. Also, Excel being a stand-alone application, there was no way to achieve this by writing custom code as you would see this across many examples in the web where you have a custom Client Application where you can write the code to get this done.

For a typical managed Client application where you have the flexibility to write custom code, there are many examples to achieve this on the Web but none for this specific scenario. For example, the detailed implementation from Microsoft is here.

The initial setup for my application was as below:

Azure API App setup

• Developed as a custom API App using Visual Studio – Azure API App template
• Developed to return some In-Memory data as below in OData format
  

  namespace ReportingWebAPI.Controllers
  {
      [ODataRoutePrefix("analysis")]
      [EnableQuery]
      public class ReportingAnalysisController : ODataController
      {
  
          [ODataRoute]
          public async Task<IHttpActionResult> Get()
          {
  
  
              List<ReportingWebAPI.Models.ReportingAnalysis> requests = new List<ReportingWebAPI.Models.ReportingAnalysis>
              {
                  {new Models.ReportingAnalysis(){ AreaCode="Code1", UnitCode="Code1", Function="SampleFunction", ID ="123124", Segment="TestSegment"} }
              };

• Sample code from the Controller as above, which returns some In-Memory data as an OData format while accessing the API controller
• Been deployed within the Azure App service and Azure AD Authentication enabled in the below manner for the specific API App from the App -> Authentication & Authorization setting
  
  

• Note that, setting up the application this way automatically registers the application within the Azure AD as an app with default permissions provided to the application to read from Azure AD as highlighted above.

This being a simple setup, what you notice is that the API is now setup within Azure to be authenticated using Azure AD credentials. The API works perfectly while accessing it from the Browser, where it prompts the end user to login using the Azure AD Credentials, signs in and returns the In-Memory data as expected.

However, Accessing the API from the Power Query, always throws the below error in the Power Query console in Excel.

The warning is “We Couldn’t authenticate with the credentials provided. Please try again.”

Solution

I couldn’t find much help on the Web and absolutely nothing documented. I could find lot of people reporting this issue from way back in 2015, where there were suggestions to change the Web API to add custom “WWW-Authenticate” headers etc. Irrespective of trying all those solutions this never worked.

Finally, with a lot of fiddler tracing and log analysis, what was observed is that the audience for what the access token was getting generated for does not match the one that the Web App was actually looking for and this resulted in a “401 Unauthorized“ Error message.

Finally, by adjusting the authentication within the Azure AD for the Web API this issue could be addressed. The Azure AD Authentication has an Advanced mode which allows specifying various Audiences, and finally setting the configuration as below resolved the issue.

Note that by changing the Azure AD Authentication setting to “Advanced” mode and specifying the Root URL of the API within the allowed token Audience as a new entry resolves this issue after which the API works from the Odata Feed query in Power Query within Excel. All other entries in the screenshot above are added by default which is to be retained as is.