Connecting Excel to OAuth API on Azure

Question

We have implemented authentication on our multi tenant SaaS application through Azure AD (which implements OAuth 2.0). The API's are accessed through an Angular SPA and can also be accessed by other clients (such as registered REST clients, with a dedicated client ID). In case it's relevant: we use the authorization code flow.

On the same application we have an OData API through which we want to provide data access to our users' applications such as Excel. Since we are a multi tenant application this connection needs to be authenticated just as when accessing the "normal" Web API's, such that our data layer can filter for data owned by that tenant.

Even though we only use Microsoft services (through Azure) it doesn't seem evident how Microsoft Excel can connect to the OData feed with the correct authentication method. I have found one article that explains using a Power Query editor with a custom connection definition. I would not consider this approach as it's not a robust solution for typical end users. In addition to this custom configuration approach, I have also read about commercial third party libraries that take over the connection. However for my SaaS customers I can't propose this as a general solution.
I have also found another article that uses an Azure function as a proxy API to get the data. This seems like a robust solution for end users, however I am not sure how this can be done securely and correctly authenticate the API for the correct user (the example in the article is dedicated to 1 tenant).

Q: Is there a robust (out-of-the-box) configuration for end users to access OAuth authenticated OData feed/API's from Excel? If not, what are some secure alternatives I should consider?

Answer 1

You can do OAuth 2.0 authentication on AzureAD from your client application such as Excel, Word, Powerpoint and Publisher. This works for opening entire files (Office 2016 or above) or import data from a webpage (Office 2019 / M365-apps).

The way you set up OAuth authentication will differ a bit from the regular and well documenten OAuth flow as you use it in your REST clients.

Instead of sending a header that forwards the client to https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize you need to return the http response code 401 (unauthorized) and add the next headers:

WWW-Authenticate: Bearer resource="https://management.azure.com/" client_id="{the client id of your registered app in Azure AD}", trusted_issuers="00000001-0000-0000-c000-000000000000@*", token_types="app_asserted_user_v1 service_asserted_app_v1", authorization_uri="https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize",Basic Realm=""'

In Azure AD in your app registration:

• In API permissions:
  • Azure Active Directory Graph > User.Read In
• Expose an API:
  • Add a scope: https://{fqdn of your api}/email (email or other attribute you use for authentication)
  • Authorized client application > Add a client application: d3590ed6-52b3-4102-aeff-aad2292ab01c (the ID of MS Office)

Note: all this is not documented at all. I found out about the headers by sniffing the traffic between Outlook and O365.

Btw: If you serve files using the webdav protocol: opening a file in Excel from a webpage works well with javascript:

location.href = "ms-excel:ofe|https://{yourAPI}/your_output.xlsx"

Answer 2

ANSWER EDITED TO FINAL WORKING VERSION

Thanks in part to comments by others on this thread, I have been able to get this working. Here is my final solution (edited from the original answer that had some of the pieces of the puzzle missing)

I registered the app in Azure, exposed the API, changed the Application ID URI to my verified app url and added the user_impersonation scope .

You will need to ensure that you have a verified Custom Domain Name in Azure AD.

I accepted the default API permissions.

I created a .net 6 WebApi project, made a simple odata controller.

[Route("odata")]
public class WBSController : BaseODataController
{
    public WBSController(IDbContextFactory<TableDbContext> db) : base(db)
    {

    }

    [Authorize(AuthenticationSchemes = JwtBearerDefaults.AuthenticationScheme)]
    [EnableQuery]
    [HttpGet("WBS")]
    public IQueryable<WBS> Get()
    {
        IQueryable<WBS> ret = Db.WBS;

        return ret;
    }
}

I then configured the API in Program.cs . (NOTE: for client Id - use your App Id Uri.

Program.cs

.AddMicrosoftIdentityWebApi(
     opt => //JwtBearerOptions
    {
        opt.Audience = "[Your App Id Uri eg. acme.com.au]";
        opt.Events = new JwtBearerEvents()
        {
            OnChallenge = async ctxv =>  
            {
                opt.Challenge =
                    @"Bearer  realm="""", client_id=""[Your App Id Uri]"", trusted_issuers=""00000001-0000-0000-c000-000000000000@*"", token_types=""app_asserted_user_v1 service_asserted_app_v1"",  authorization_uri=""https://login.microsoftonline.com/[Your Tenant Id]/oauth2/v2.0/authorize""";  //This is the magic that forces the Microsoft login dialog to open
            }
        };
    },
    opt =>   //MicrosoftIdentityOptions
    {
        opt.Instance = "https://login.microsoftonline.com/";
        opt.ClientId = "[Your App Id Uri]";
        opt.TenantId = "common";
    },
    $"{JwtBearerDefaults.AuthenticationScheme}"  //Scheme Name
);

I thought this was a big breakthrough as I was able to pick an account and sign-in.

If you come across the following error, it means that you have not set the App ID in Azure AD to your verified application url: invalid_resource:AADSTS500011: The resource principal ...... was not found in the tenant named .....

If you get any errors at all, or it doesn't do anything, run a trace using Fiddler. That is how I determined it was looking for the user_impersonation scope.

If you get all of these steps right, you should end up with a refreshable table of your OData data in Excel.

I note that MS says it is not supported to connect to "arbitrary" services like this. https://learn.microsoft.com/en-us/power-query/connectors/odatafeed#authenticating-to-arbitrary-services.

However I believe that this is a very good way of providing Excel super users the ability to access structured data from a Web Api. I hope this post saves you the many hours it took me to get this going.