How do you implement authentication using Entra ID, OAuth, and JWT?

Authentication with Entra ID, OAuth 2.0, and JWT

These three pieces fit together as follows:

• Microsoft Entra ID is the identity provider (IdP). It authenticates users and apps and issues tokens.
• OAuth 2.0 is the authorization framework: how a client gets permission to call an API on behalf of a user or as itself.
• OpenID Connect (OIDC) is a layer on OAuth 2.0 for sign-in. It adds an ID token that tells your app who the user is.
• JWT is the token format. Entra issues access tokens and ID tokens as signed JSON Web Tokens.

Access tokens are for APIs ("this caller may do X"). ID tokens are for the client app ("this user signed in"). Never send an ID token to an API.

1. Choose the flow

Scenario	Flow
SPA or mobile app, user signs in, calls your API	Authorization code + PKCE
Server-side web app with user sign-in	Authorization code (with a client secret or certificate)
Service-to-service, no user (daemon, background job)	Client credentials
API calls another API as the signed-in user	On-behalf-of (OBO)

Avoid the implicit flow and the password (ROPC) flow. Both are legacy and discouraged.

2. Register the apps in Entra ID

You typically register two apps: one for the API and one for the client.

API registration:

1. Go to Entra ID → App registrations → New registration.
2. Under Expose an API, set the Application ID URI (e.g. api://<api-client-id>) and add a scope such as access_as_user for delegated (user) permissions.
3. Under App roles, add roles such as Orders.Read for app-only callers (and for user roles).
4. In the manifest, make sure requestedAccessTokenVersion is 2 so you get v2.0 tokens.

Client registration:

1. Add a redirect URI. Use the SPA platform for SPAs, which enables PKCE, or Web for server apps.
2. Under API permissions, add your API's scope or role and grant admin consent if needed.
3. For confidential clients, add a certificate (preferred) or client secret under Certificates & secrets.

3. Protect an ASP.NET Core API (validate JWTs)

dotnet add package Microsoft.Identity.Web

appsettings.json:

{
  "AzureAd": {
    "Instance": "https://login.microsoftonline.com/",
    "TenantId": "<tenant-id>",
    "ClientId": "<api-client-id>",
    "Audience": "api://<api-client-id>"
  }
}

Program.cs:

using Microsoft.Identity.Web;

var builder = WebApplication.CreateBuilder(args);

builder.Services
    .AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddMicrosoftIdentityWebApi(builder.Configuration.GetSection("AzureAd"));

builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("OrdersRead", p => p.RequireRole("Orders.Read"));
});

var app = builder.Build();

app.UseAuthentication();
app.UseAuthorization();

app.MapControllers();
app.Run();

Controller:

[Authorize]
[ApiController]
[Route("api/orders")]
public class OrdersController : ControllerBase
{
    // Delegated (user) token: check the scope claim
    [HttpGet]
    [RequiredScope("access_as_user")]
    public IActionResult GetMine() => Ok(new { user = User.Identity?.Name });

    // App-only token (client credentials): check the roles claim
    [HttpGet("all")]
    [Authorize(Policy = "OrdersRead")]
    public IActionResult GetAll() => Ok();
}

User tokens carry scopes in the scp claim, and app-only tokens carry app roles in the roles claim. Check the right one for the flow you expect.

4. Sign users in to a web app (OIDC)

builder.Services
    .AddAuthentication(OpenIdConnectDefaults.AuthenticationScheme)
    .AddMicrosoftIdentityWebApp(builder.Configuration.GetSection("AzureAd"))
    .EnableTokenAcquisitionToCallDownstreamApi(new[] { "api://<api-client-id>/access_as_user" })
    .AddInMemoryTokenCaches();

Inject ITokenAcquisition to get an access token for the signed-in user and call your API:

var token = await _tokenAcquisition.GetAccessTokenForUserAsync(
    new[] { "api://<api-client-id>/access_as_user" });

_http.DefaultRequestHeaders.Authorization =
    new AuthenticationHeaderValue("Bearer", token);

For a SPA, use MSAL.js (@azure/msal-browser, or @azure/msal-react / @azure/msal-angular). It handles the code + PKCE flow, token caching, and silent refresh:

const msal = new PublicClientApplication({
  auth: { clientId: "<spa-client-id>", authority: "https://login.microsoftonline.com/<tenant-id>" }
});

const result = await msal.acquireTokenPopup({
  scopes: ["api://<api-client-id>/access_as_user"]
});

fetch("https://my-api/api/orders", {
  headers: { Authorization: `Bearer ${result.accessToken}` }
});

5. Service-to-service (client credentials)

When there's no user, the caller authenticates as itself. Always request the .default scope, which returns the app roles granted to that client:

var credential = new DefaultAzureCredential(); // managed identity in Azure

var token = await credential.GetTokenAsync(
    new TokenRequestContext(new[] { "api://<api-client-id>/.default" }));

Prefer a managed identity over a client secret. Assign the API's app role to the managed identity's service principal (via Graph, PowerShell, or Azure CLI). Outside Azure, use ConfidentialClientApplication from Microsoft.Identity.Client with a certificate.

6. What your API must validate in a JWT

A JWT has three base64url parts, header.payload.signature. AddMicrosoftIdentityWebApi does the following for you, but you should know what's being checked:

• Signature: verified using Entra's public signing keys, fetched from the OIDC metadata endpoint (.../.well-known/openid-configuration → jwks_uri) and cached and rotated automatically.
• iss (issuer): must be your tenant, e.g. https://login.microsoftonline.com/<tenant-id>/v2.0.
• aud (audience): must be your API (its client ID or Application ID URI). A token meant for another API must be rejected.
• exp / nbf: token is within its validity window (access tokens last roughly 60 to 90 minutes).
• scp or roles: the caller has the permission required for this operation.
• tid: for multi-tenant APIs, check that the tenant is one you allow.

Key claims you'll use: oid (user or service principal object ID), sub, tid, scp, roles, preferred_username, appid/azp (the calling client).

If you ever need manual configuration without Microsoft.Identity.Web:

.AddJwtBearer(o =>
{
    o.Authority = "https://login.microsoftonline.com/<tenant-id>/v2.0";
    o.TokenValidationParameters = new()
    {
        ValidAudience = "api://<api-client-id>",
        ValidateIssuer = true,
        ValidateAudience = true,
        ValidateLifetime = true
    };
});

7. Common pitfalls

• Mixing up token types: APIs accept access tokens only, never ID tokens.
• Wrong audience or token version: a v1 token has a different iss and aud format than v2, so set the API's accepted token version to match your validation.
• Checking only authentication: a valid token isn't enough. Always check scopes or roles, otherwise any app in your tenant that can get a token for your API is authorized.
• Storing tokens in localStorage: this exposes them to XSS. Let MSAL manage caching, or use a backend-for-frontend (BFF) with HttpOnly cookies.
• Client secrets in code or SPAs: public clients (SPAs, mobile) can't keep secrets, which is why they use PKCE.
• Decoding without validating: reading claims from a JWT without verifying its signature proves nothing.
• Forgetting admin consent for application permissions, which causes 403s.
• Rolling your own JWT handling: use MSAL and Microsoft.Identity.Web rather than hand-parsing tokens and keys.

8. Alternative: let Azure handle it (Easy Auth)

On App Service or Azure Functions, built-in authentication validates the token before your code runs and passes claims to your app via headers (X-MS-CLIENT-PRINCIPAL). It's the least-code option, though you get less control over fine-grained authorization than with middleware in your own code.

Summary

1. Register an API (with scopes and app roles) and a client in Entra ID.
2. Use auth code + PKCE for users and client credentials (ideally with a managed identity) for services.
3. The client acquires an access token via MSAL and sends it as Authorization: Bearer <token>.
4. The API validates the JWT (signature, issuer, audience, expiry) and enforces scopes/roles.

Write a query to find employees with salary greater than average salary in the entire company, ordered by salary descending.

To find employees whose salary is greater than the company-wide average, you cannot simply place WHERE salary > AVG(salary) in a standard query because aggregate functions are not allowed in the WHERE clause without grouping.

Instead, you must use a Subquery or a Window Function.

Here are the two best ways to write this query, followed by a complete playground script.

---

Method 1: Using a Subquery (Standard & Highly Compatible)

This method calculates the company-wide average first in an isolated subquery, then uses that single numeric value to filter the main table.

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Method 2: Using a Common Table Expression (CTE) & Window Function

This approach calculates the average alongside your other data columns using AVG() OVER(), allowing you to see exactly how much each employee exceeds the average.

sql

WITH SalaryAnalysis AS (
    SELECT 
        employee_id,
        employee_name,
        department,
        salary,
        AVG(salary) OVER() AS company_average
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROUND(company_average, 2) AS company_average
FROM SalaryAnalysis
WHERE salary > company_average
ORDER BY salary DESC;

Use code with caution.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
-- Total Salary = 430,000 | Average Salary = 86,000
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),  -- Above Average
(2, 'Bob Jones', 'Sales', 75000.00),            -- Below Average
(3, 'Diana Prince', 'Marketing', 82000.00),      -- Below Average
(4, 'Fiona Gallagher', 'Engineering', 110000.00),-- Above Average
(5, 'Hannah Abbott', 'Marketing', 68000.00);    -- Below Average

-- 3. EXECUTE FILTERED QUERY
SELECT 
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Use code with caution.

Expected Query Output

With a company average of 86,000.00, the query filters out the lower salaries and yields:

employee_name	department	salary
Fiona Gallagher	Engineering	110000.00
Alice Smith	Engineering	95000.00

Write a query to calculate the difference between current row and previous row's salary (lag function).

To calculate the difference between the current row's salary and the previous row's salary, you use the LAG() window function. LAG() allows you to look backward at a previous row's data within your result set without executing a complex self-join.

The Query

Here is the standard SQL query. It orders the employees by salary (highest to lowest) and computes the exact structural difference between adjacent positions:

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Step 1: Fetch the previous row's salary
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary,
    
    -- Step 2: Subtract the previous row's salary from the current row's salary
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_difference
FROM employees;

Use code with caution.

Syntax Deep Dive: LAG(column, offset, default)

• salary: The target column you want to retrieve a historical value from.
• 1 (Offset): How many rows to look backward. A value of 1 targets the immediate previous row.
• 0 or salary (Default Value): What to return if there is no previous row (e.g., the very first row in the dataset).
  • Passing 0 or omitting it helps show a blank baseline.
  • Passing the column itself (salary) ensures that subtracting it from the current row results in a clean 0 instead of a breaking NULL value on row one.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the calculations live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Diana Prince', 'Marketing', 82000.00),
(4, 'Fiona Gallagher', 'Engineering', 110000.00),
(5, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE LAG QUERY
SELECT 
    employee_name,
    department,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_employee_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_diff_from_previous
FROM employees;

Use code with caution.

Expected Query Output

employee_name	department	salary	previous_employee_salary	salary_diff_from_previous
Fiona Gallagher	Engineering	110000.00	NULL	0.00
Alice Smith	Engineering	95000.00	110000.00	-15000.00
Diana Prince	Marketing	82000.00	95000.00	-13000.00
Bob Jones	Sales	75000.00	82000.00	-7000.00
Hannah Abbott	Marketing	68000.00	75000.00	-7000.00

(Note: Fiona displays 0.00 difference because she is at the top of the chain; there is no previous record to subtract from her total.)