Matthew DesEnfants

Posted on Jan 11, 2023 • Updated on May 11, 2023

Custom .NET 6 OData Service with OAuth for Power Query

#dotnet #aspnetcore #odata #oauth2

Overview

This article gives you all the context and examples you need to connect Power Query, Power BI, or Excel to an organization's SSO-enabled ASP.NET Core OData endpoint. If you found this page, you probably have a very niche Microsoft-technology-focused scenario that was not adequately documented among its product groups.

The What

OData is an open standard similar to GraphQL. It lets you quickly filter, sort, and select the final shape of data coming from an otherwise ordinary REST API. This gives some obvious advantages. For example, a product group's UX team can easily consume just the data they need, while the API team just focuses on making the data available and performant.

The Why

GraphQL is great, but OData does have the distinct advantage of being really old. It was develped for many of the same reasons back when JSON was just a twinkle in Douglas Crockford's eye. If you swim in Microsoft circles, particularly around business applications, you may have heard used it before. OData has gotten progressively better over the years, including the ability to serialize to JSON for more modern web applications.

But if we have GraphQL, why use OData?

Well... because sometimes that's your only option.

Common enterprise-level analtyics tools (Tableu, Power BI, Python) tend to have native integration for OData endpoints even if they don't understand GraphQL. They even support Single Sign-On scenarios through OAuth (when the API supports it). Power Query, for example, lets Power BI and Excel connect almost seamlessly to OData endpoints, especially public ones. Once you want to secure that OData endpoint, though, things start to get more challenging.

The Real-World Problem

Our SaaS's onboarding process had one sticky manual step: tracking utilization for our invoice reports. Attaching those reports to a new tenant database was required for each new customer, even though all the other tenant deployment steps were automated. That said, the existing reports worked very well, and we needed to preserve as much of that functionality as possible without breaking our security boundaries.

A couple requirements naturally emerged.

Security through Single Sign-On (Azure AD)
Out-of-the-box compatibility with Power Query

Basically, we wanted to make sure our business team could build reports without having to store and transmit some sort of connection string/API key.

The Hiccups

Once I started testing the OAuth version of our API with Power Query, things started to unravel. Just to give you an idea of the problems you might encounter...

The 'localhost' domain is not a supported endpoint, so you have to test with a live URL.
Your app service must have a custom verified domain (e.g. no azurewebsites.net subdomains)
Your app must provide a WWW-Authenticate header that tells Power Query where to authenticate
Your app configuration must recognize the verified domain as the host and audience
Your app registration must have a user_impersonation scope configured
Power Query authentication errors are deliberately vague
There don't appear to be any consolidated examples using modern ASP.NET + OAuth + OData + Power Query

Maybe you'll even encounter a couple errors like these...

AADSTS500011: The service principal named [https://localhost:5555] was not found on the tenant [tenant name]
AADSTS65005: The application 'Microsoft Power Query for Excel' asked for scope 'user_impersonation' that doesn't exist

The Example

There are a lot of references on how to make a new minimal Web API with .NET 6, so I'm going to focus on the key classes.

As a base, I'm using the most recent version of the Weather Web API that comes with Visual Studio 2022 and modified it according to this blog post by Hassan Habib. His example serves a simple in-memory POCO database of weather forecasts and configures you the tools to query it.

Modifying Program.cs

If you're starting with the basic top level statements template for Web API and follow the tutorial by Hassan, you will end up with simple app that exposes an existing API controller with OData filtering/sorting/etc. The OData configuration portion is done!

Next, if you're using Azure AD for your SSO provider, you'll want to enable authentication with these lines after your builder.Services.AddControllers call.

builder.Services.AddMicrosoftIdentityWebApiAuthentication(builder.Configuration)
    .EnableTokenAcquisitionToCallDownstreamApi()
    .AddInMemoryTokenCaches();

From there, go to your application config file and add the Azure AD section like you would for any other Azure AD endpoint.

"AzureAd": {
    "Instance": "https://login.microsoftonline.com/",
    "ClientId": "<tbd>",
    "TenantId": "<Your Tenant ID>",
    "Audience": "<tbd>"
  }
}

Now for the unusual part.

Out of the box, your authentication headers don't provide enough information for Power Query to know where to get a token. To do that, you have to add the following.

app.Use(async (context, next) =>
{
    // When there is no "Authorization: Bearer" provided, provide the realm
    if (context.Response.StatusCode == 401)
    {
        //
        context.Response.Headers["www-authenticate"] = $"Bearer realm=https://login.microsoftonline.com/{builder.Configuration["AzureAd:TenantId"]}";
    }
    // When "Authorization: Bearer" is sent, provide the authorization endpoint
    else
    {
        context.Response.Headers["www-authenticate"] = $"Bearer authorization_uri=https://login.microsoftonline.com/{builder.Configuration["AzureAd:TenantId"]}/oauth2/v2.0/authorize";
    }

    await next();
});

When Power Query first sends an auth request, it comes in the form of Authorization: Bearer without a token. The two types of headers you return are similar to ones you might have seen in a Basic auth scenario with an API key. You specify the authentication "realm" through which PowerQuery can perform a token acquisition. From there, Power Query can request the authorization endpoint you want to issue your token.

Once Power BI gets the token, it sends an Authorization: Bearer eyj... token as you would expect from a JWT-enabled web client. As long as the app configuration is correct, you're home free on the code front.

The Verified Domain

One very unfortunate issue with Power Query and Azure AD is that you cannot connect to a localhost endpoint. You can test your API with Postman or other tools locally, but Power Query demands a fully qualified domain on a public server. Otherwise, it will not generate the correct token with the correct audience claim.

Not only is localhost forbidden, but Power Query also expects the audience to match the URL to the service. The GUID-based app ID does not work.

Before you continue, make sure you have a fully qualified domain ready to use for your Azure Web App.

Creating an Azure AD App Registration

Create an app registration as you would for a single page application like this, but make the redirect URL point to your soon-to-be-verified URL.

We'll go with https://yourverifieddomain.example for this post.

You can add your Tenant ID to your configuration files at this point. The Client ID will not help you yet, so you can leave it blank.

Generate an application secret you can add to your App Service config. Save it somewhere safe for a few minutes.

Creating Your App Service with Custom Verified Domain

Create a new .NET 6 App Service in Azure using the ASP.NET 6 example.

In Azure, ensure that your app service has the following configuration settings:

ASPNETCORE_ENVIRONMENT: Production
AZURE_CLIENT_SECRET: from your app registration

Configure this from your app registration: AZURE_CLIENT_SECRET

Client ID will not help you yet. First, you need to set up your verified domain. A new Client ID that's compatible with Power Query will be available once that is done.

Finally, create a custom domain on your app service following these instructions.

After the domain is listed as "verified" and it indicates that the certificate is ready, you can move on to the final steps.

Completing the Azure AD App Registration

Now that you have a verified public domain, you can finish setting up your app registration to expose your new API endpoint.

Go to the app registration in Azure AD and Expose an API.

Next, you will...

Add an Application ID URI matching your verified domain.
Configure the user_impersonation scope
Add the Client ID for Power Query: a672d62c-fc7b-4e81-a576-e60dc46e951d

I added an extra OData.Read scope for future use, but that's not necessary. Your app registration should now look like this.

Updating the app service code and configuration

If you previously used the GUID version of your Client ID, you will need to replace it with your verified URL. Find any instance of these and enter the new API identifier.

AzureAd:ClientId (appsettings.json): https://yourverifieddomain.example

Your ASP.NET application will pull that value for both the authorization endpoint and the audience claim on the JWT token. Power Query will expect this to match the domain of the OData service URL.

Testing with Power Query

I'm going to assume that if you came this far, you have access to either Excel or Power BI Desktop. Whichever you're using, start a new project and choose OData as your data source.

You'll be presented with a few authentication options ranging from Anonymous (not for us!) down to Organization Credentials. To use your SSO, you'll want the Org option.

After a successful authentication routine, Power BI or Excel should present you with a list of your OData entities. Congratulations, you can go home!

Optional: Token generation

I found it helpful to be able to grab my own personal token to test with Postman or cUrl. For that, I made a little command utility with top-level statements. We have two environments for test and production, so the app accepts a command line parameter to point me to the correct endpoint and scopes.

using Azure.Identity;
using System.Net.Http.Headers;

foreach (var environment in args)
{
    var scopes = new[]
    {
$"https://{environment}.yourverifieddomain.example/user_impersonation"
    };

    var result = await new InteractiveBrowserCredential()
        .GetTokenAsync(new Azure.Core.TokenRequestContext(scopes));

    var httpClient = new HttpClient();
    httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.Token);

    Console.WriteLine("Bearer " + result.Token);
}

Conclusion

Securing an OData endpoint for use with Power Query in ASP.NET 6 is both a mouthful and a head-scratcher. Once you have the basics, the main hurdles are ensuring the correct domain, scope, and headers to guide Power Query to the right auth token.

Obviously, an in-memory database is the most trivial example you could follow. In our real-world example, we were connecting to Azure SQL Server via Dapper using a read-only Azure Default Credential, then presented our invoice data as an aggregation over multiple tenant databases.

You could extend this to use entity framework to take advantage of native query optimization. You could also expose any number of POCO-enabled data sources.

I hope you found this helpful and saved you some time. If you have any questions, you can find me on LinkedIn.

References

Power Query Supported Workflow

AADSTS65005 Access Denied

Up and Running with OData in ASP.NET 6

Image by Nicholas Cappello via Unsplash.

DEV Community