Excel We Couldnt Authenticate With the Credentials Provided Please Try Again

Objective

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

I besides had a similar requirement and to add together more on top, my requirement was to develop an API which could render information in the OData format so that the API could be consumed from the Excel Ability Query but at the same time the API needed to be secure with Azure Advert Hallmark. The requirement was extremely logical since the terminate users could use the Excel Ability query to connect to an API in Azure which returned data in the OData format using their Organizational Account and permit them play around with the information in Excel.

My intention was to achieve this with a No-Code solution since the Azure API Apps already provided the capability to prepare the authentication machinery 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 higher up shows how an Azure API App is set to exist authenticated with Azure Active Directory.

The screenshot higher up shows the manner the Odata-Feed connectivity choice in Ability Query provides the capability to authenticate utilizing Azure Advertisement accounts.

Problem Statement

Irrespective of the fact that the Objective is simple, it was extremely difficult to get this done because information technology is not yet documented in item on how this could be accomplished. Also, Excel being a stand-alone awarding, there was no way to achieve this by writing custom code as you lot would see this beyond many examples in the web where you have a custom Client Awarding where you tin can write the code to become this done.

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

The initial setup for my application was every bit beneath:

Azure API App setup

• Adult equally a custom API App using Visual Studio – Azure API App template
• Developed to return some In-Retention 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 Job<IHttpActionResult> Get()
  10.         {
  13.             Listing<ReportingWebAPI.Models.ReportingAnalysis> requests =new  List<ReportingWebAPI.Models.ReportingAnalysis>
  14.             {
  15.                 {new  Models.ReportingAnalysis(){ AreaCode= "Code1" , UnitCode= "Code1" , Part= "SampleFunction" , ID = "123124" , Segment= "TestSegment" } }
  16.             };

• Sample lawmaking from the Controller as to a higher place, which returns some In-Retentivity data as an OData format while accessing the API controller
• Been deployed inside the Azure App service and Azure AD Hallmark enabled in the below manner for the specific API App from the App -> Authentication & Dominance setting

  

• Notation that, setting upwardly the awarding this fashion automatically registers the application within the Azure Advert as an app with default permissions provided to the application to read from Azure AD as highlighted higher up.

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

Nonetheless, Accessing the API from the Ability Query, always throws the below error in the Power Query panel in Excel.

The warning is "Nosotros Couldn't cosign with the credentials provided. Please try over again."

Solution

I couldn't detect much help on the Web and absolutely nothing documented. I could detect lot of people reporting this issue from fashion dorsum 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 non match the ane that the Web App was actually looking for and this resulted in a "401 Unauthorized" Fault message.

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

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

younginsed1960.blogspot.com

Source: https://www.c-sharpcorner.com/article/azure-odata-feed-web-api-authentication-setup-with-azure-ad/

Share this post