Azure OData Feed Web API Authentication Setup With Azure AD

• 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
  
  1. namespace ReportingWebAPI.Controllers
  2. {
  3. [ODataRoutePrefix("analysis")]
  4. [EnableQuery]
  5. public class ReportingAnalysisController : ODataController
  6. {
  8. [ODataRoute]
  9. public async Task<IHttpActionResult> Get()
  10. {
  13. List<ReportingWebAPI.Models.ReportingAnalysis> requests = new List<ReportingWebAPI.Models.ReportingAnalysis>
  14. {
  15. {new Models.ReportingAnalysis(){ AreaCode="Code1", UnitCode="Code1", Function="SampleFunction", ID ="123124", Segment="TestSegment"} }
  16. };

• 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.