How do you troubleshoot a production API issue in Azure?

Troubleshooting a Production API Issue in Azure

The goal is to stabilize first, diagnose second, fix third. Work from the outside in: is it the platform, the network, your code, or a dependency?

1. Triage (first 5 minutes)

Establish the facts before touching anything:

• What is the symptom? Errors (5xx/4xx), slowness, timeouts, or total outage?
• Scope: all users or some? All endpoints or one? One region or instance, or everywhere?
• When did it start? Line it up against deployments, config changes, certificate expiries, and traffic spikes. Most incidents trace back to a recent change.
• Is it Azure? Check Azure Service Health and Resource Health for your subscription and the specific resource. Platform incidents and maintenance show up here.

If a recent deployment is the likely cause, roll back first and investigate later. With deployment slots, swap back. That's the fastest mitigation you have.

2. Check the golden signals in Application Insights

Open Application Insights and go through these in order:

• Failures blade: which operations and exception types are spiking, with sample failed requests.
• Performance blade: which operations got slower, and whether the time is in your code or in a dependency.
• Application Map: which node is red (slow or failing). This shows quickly whether the API or something it calls is at fault.
• Live Metrics: real-time view while you test or mitigate.
• End-to-end transaction details: follow a single failed request through all services using its operation ID.

Useful KQL:

// Failure trend by result code
requests
| where timestamp > ago(2h)
| summarize total = count(), failed = countif(success == false) by bin(timestamp, 5m)
| extend failureRate = todouble(failed) / total
| render timechart

// Is it a dependency?
dependencies
| where timestamp > ago(2h) and success == false
| summarize count() by target, type, resultCode
| order by count_ desc

// Latency percentiles by operation
requests
| where timestamp > ago(2h)
| summarize p50 = percentile(duration, 50), p95 = percentile(duration, 95), count() by name
| order by p95 desc

// Top exceptions
exceptions
| where timestamp > ago(2h)
| summarize count() by type, outerMessage
| order by count_ desc

Compare the bad period against the same time yesterday or last week to separate real regressions from normal patterns.

3. Narrow down by symptom

Symptom	Likely causes	Where to look
500s after a deploy	Bug, missing config, bad migration	Exceptions, deployment history, diff of app settings
500s / timeouts, no deploy	Failing dependency (SQL, Redis, downstream API), expired secret or cert	Dependencies table, Application Map
401/403	Expired token or secret, changed app role or RBAC, clock/audience mismatch, rotated key	Entra sign-in logs, role assignments, Key Vault access logs
429	Throttling by a downstream service (Cosmos DB RU limits, Key Vault, Storage) or by APIM	Response headers, service metrics
502/503/504	App crashing or restarting, instances unhealthy, gateway timeout (App Gateway/Front Door ~230s limit on App Service)	Health check status, restarts, Diagnose and solve problems
Slow responses	Slow SQL queries, cold starts, thread pool starvation, CPU/memory pressure, chatty calls	Performance blade, dependency durations, App Service metrics
Intermittent failures	One bad instance, SNAT port exhaustion, transient faults without retries, DNS issues	Per-instance breakdown (cloud_RoleInstance), connection metrics
Works in one place, not another	NSG/firewall/private endpoint/DNS change	Network diagnostics (below)

4. Platform-level diagnostics

App Service / Functions:

• Diagnose and solve problems blade: guided detectors for HTTP 5xx, high CPU, memory, slow requests, SNAT exhaustion, and app crashes. Start here when the cause isn't obvious.
• Metrics: CPU, memory working set, requests, response time, HTTP 5xx, and instance count. Look for saturation or a scale-out that didn't happen.
• Log stream for live output: az webapp log tail --name my-app --resource-group my-rg
• Kudu (SCM) console for process explorer, environment variables, and crash dumps.
• Health check: verify the path is configured and which instances are being removed from rotation.

Containers / AKS / Container Apps:

• kubectl get pods, kubectl describe pod, kubectl logs --previous for restart loops (CrashLoopBackOff, OOMKilled).
• Check readiness and liveness probes, resource limits, and node health.
• For Container Apps, use the Log stream and ContainerAppConsoleLogs / ContainerAppSystemLogs tables.

APIM / Front Door / Application Gateway (if in front):

• Check whether failures originate at the gateway or the backend. Gateway logs show backend response codes, backend time vs. total time, and WAF blocks.
• A WAF rule or rate limit blocking legitimate traffic often looks like an app problem.

5. Check dependencies

Most production API incidents originate in something the API calls:

• Azure SQL: Query Performance Insight, blocking and deadlocks, DTU/vCore saturation, connection limits, failover events. Check whether a new query plan or missing index is the cause.
• Cosmos DB: 429s and RU consumption, hot partitions.
• Service Bus / Event Hubs: growing queue depth, dead-letter counts, lock-expired errors, throttling.
• Key Vault: throttling, or access denied after a role or network change.
• Redis: memory pressure, evictions, connection limits.
• Storage: throttling and latency metrics.
• Third-party APIs: check their status pages and your outgoing call durations.

6. Network and connectivity

If the app can't reach something or can't be reached:

• Network Watcher: Connection troubleshoot, IP flow verify, and NSG flow logs show whether a rule is dropping traffic.
• DNS: private endpoints need correct private DNS zone links. From Kudu, run nameresolver <host> and tcpping <host:port> (on Windows App Service).
• Firewall and access restrictions: recent changes to storage, SQL, or Key Vault firewalls often cause sudden 403s or timeouts.
• SNAT port exhaustion: many outbound connections to the same endpoint can exhaust ports. Reuse HttpClient through IHttpClientFactory, use connection pooling, or add a NAT Gateway.
• TLS and certificates: expired or rotated certificates cause sudden handshake failures.

7. Compare against change history

Ask "what changed?" and verify it from records, not memory:

• Activity Log: shows control-plane changes (config updates, scaling, role assignments, deployments, key rotations). Filter by time window around the incident start.
• Deployment history / CI-CD pipeline runs and any app setting changes.
• Entra audit logs: app registration, secret, or permission changes.
• Resource Graph or Change Analysis (in Diagnose and solve problems) to see configuration diffs.

8. Go deeper when needed

• Memory or CPU issues: capture a memory dump or profiler trace through Diagnose and solve problems (Collect Memory Dump, Profiler) or dotnet-dump/dotnet-trace in containers.
• Thread pool starvation (common with sync-over-async): look for rising response times with low CPU, and check ThreadPool metrics in Application Insights or dotnet-counters.
• Reproduce in a lower environment using the same request payload and the operation ID details from the failure.
• Increase logging temporarily for the affected component, then revert it. Sampling can hide rare failures, so check whether it's dropping the data you need.

9. Mitigate while you investigate

• Roll back or swap slots.
• Scale up/out if it's resource saturation (buys time, doesn't fix the cause).
• Restart the app or a single bad instance as a last resort. Note the time and capture evidence first, because a restart destroys state.
• Disable the faulty feature with a feature flag, or route traffic away (Front Door / Traffic Manager) from an unhealthy region.
• Add retries with backoff and circuit breakers (Polly, or Microsoft.Extensions.Http.Resilience) if a dependency is flaky.
• Communicate: post status updates early, even if the cause is unknown.

10. Afterwards

• Write a blameless post-incident review: timeline, root cause, detection gap, and action items.
• Add the missing alert that would have caught it sooner (failure rate, p95 latency, dependency failures, queue depth, certificate and secret expiry).
• Add health checks, synthetic availability tests (Standard tests), and dashboards for the signals you had to hunt for.
• Make the fix safer to ship: deployment slots, canary releases, automated rollback, and load testing for the failure mode.

Quick checklist:

1. Check Service Health and recent changes (deploys, config, secrets, certs).
2. Open Application Insights: Failures, Performance, Application Map.
3. Decide: my code, a dependency, or the platform/network?
4. Pull logs and traces for a single failing operation ID.
5. Mitigate (rollback, scale, failover) before the deep dive if users are affected.
6. Confirm recovery from the metrics, then run the post-incident review.

How do you implement logging and monitoring using Application Insights?

Logging and Monitoring with Application Insights

Application Insights is the application performance monitoring (APM) feature of Azure Monitor. It collects requests, dependencies (SQL, HTTP, Service Bus), exceptions, traces, custom metrics, and custom events, and gives you tools to query, visualize, and alert on them.

Current Application Insights resources are workspace-based: the data is stored in a Log Analytics workspace, so you can query it alongside other Azure logs with KQL. Classic (non-workspace) resources are retired, so create new ones as workspace-based.

1. Create the resource

az monitor log-analytics workspace create \
  --resource-group my-rg --workspace-name my-logs --location centralindia

az monitor app-insights component create \
  --app my-app-insights --resource-group my-rg --location centralindia \
  --workspace my-logs --kind web

Get the connection string (use this, not the old instrumentation key alone):

az monitor app-insights component show --app my-app-insights \
  --resource-group my-rg --query connectionString -o tsv

2. Instrument a .NET app

There are two options. Microsoft's recommended path for new ASP.NET Core apps is the Azure Monitor OpenTelemetry Distro, which replaces the older Application Insights SDK as the main approach. The classic SDK still works and is fine for existing apps.

Option A: Azure Monitor OpenTelemetry (recommended)

dotnet add package Azure.Monitor.OpenTelemetry.AspNetCore

using Azure.Monitor.OpenTelemetry.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenTelemetry().UseAzureMonitor();

var app = builder.Build();

Set the connection string via configuration (not in code):

APPLICATIONINSIGHTS_CONNECTION_STRING=InstrumentationKey=...;IngestionEndpoint=https://...

This automatically collects incoming requests, outgoing HTTP calls, SQL calls, exceptions, ILogger output, and performance counters.

Option B: Classic Application Insights SDK

dotnet add package Microsoft.ApplicationInsights.AspNetCore

builder.Services.AddApplicationInsightsTelemetry();

Again, provide APPLICATIONINSIGHTS_CONNECTION_STRING through app settings or environment variables.

Azure Functions (isolated worker)

dotnet add package Microsoft.Azure.Functions.Worker.ApplicationInsights

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureServices(services =>
    {
        services.AddApplicationInsightsTelemetryWorkerService();
        services.ConfigureFunctionsApplicationInsights();
    })
    .Build();

Note that the isolated worker applies a default logging filter that can hide Information logs from your code. If your logs are missing, remove it in ConfigureLogging.

App Service with no code changes

For App Service, you can enable Application Insights under the app's Monitoring blade (auto-instrumentation, "codeless"). It's the quickest start, but code-based instrumentation gives you more control for custom telemetry.

3. Logging with ILogger

Use the standard .NET ILogger<T>. Application Insights captures it automatically, and your structured properties become searchable custom dimensions.

public class OrdersController : ControllerBase
{
    private readonly ILogger<OrdersController> _logger;

    public OrdersController(ILogger<OrdersController> logger) => _logger = logger;

    [HttpPost]
    public IActionResult Create(Order order)
    {
        _logger.LogInformation("Creating order {OrderId} for customer {CustomerId}",
            order.Id, order.CustomerId);
        try
        {
            // ...
            return Ok();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to create order {OrderId}", order.Id);
            throw;
        }
    }
}

Use message templates with named placeholders, not string interpolation. Templates keep the properties as structured fields you can filter on.

Set log levels in appsettings.json. Note the provider-specific section so you control what is sent to Application Insights:

{
  "Logging": {
    "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" },
    "ApplicationInsights": { "LogLevel": { "Default": "Information" } }
  }
}

4. Custom telemetry

Events and metrics (with the OpenTelemetry distro, use System.Diagnostics.Metrics; with the classic SDK, use TelemetryClient):

// Custom metric with OpenTelemetry / .NET Meter
static readonly Meter Meter = new("MyApp.Orders");
static readonly Counter<long> OrdersCreated = Meter.CreateCounter<long>("orders_created");

OrdersCreated.Add(1, new KeyValuePair<string, object?>("region", "EU"));

Register the meter so it's exported:

builder.Services.AddOpenTelemetry()
    .UseAzureMonitor()
    .WithMetrics(m => m.AddMeter("MyApp.Orders"));

// Classic SDK
_telemetryClient.TrackEvent("OrderPlaced",
    new Dictionary<string, string> { ["region"] = "EU" });
_telemetryClient.GetMetric("OrderValue").TrackValue(order.Total);

Custom spans (distributed tracing) with ActivitySource:

static readonly ActivitySource Source = new("MyApp.Orders");

using var activity = Source.StartActivity("ProcessPayment");
activity?.SetTag("order.id", order.Id);

Register it with .WithTracing(t => t.AddSource("MyApp.Orders")).

5. Distributed tracing across services

Application Insights correlates calls across services using the W3C traceparent header. Requests, dependencies, logs, and exceptions that belong to one operation share an operation ID, so you can follow a single user action across APIs, queues, and functions. Use Application Map and the End-to-end transaction details view to see this visually. Service Bus and HTTP calls propagate context automatically with the standard Azure SDKs.

Set a meaningful cloud role name so each service appears as its own node on the map:

builder.Services.AddOpenTelemetry()
    .ConfigureResource(r => r.AddService("orders-api"))
    .UseAzureMonitor();

6. Querying with KQL

In Application Insights → Logs (or the Log Analytics workspace):

// Failed requests in the last hour
requests
| where timestamp > ago(1h) and success == false
| summarize count() by resultCode, name
| order by count_ catch desc

Correction: catch isn't valid KQL there. The correct form is:

requests
| where timestamp > ago(1h) and success == false
| summarize failures = count() by resultCode, name
| order by failures desc

// Slowest operations (p95)
requests
| where timestamp > ago(24h)
| summarize p95 = percentile(duration, 95) by name
| order by p95 desc

// Exceptions with context
exceptions
| where timestamp > ago(24h)
| project timestamp, type, outerMessage, operation_Id, cloud_RoleName

// All telemetry for one operation
union requests, dependencies, traces, exceptions
| where operation_Id == "<id>"
| order by timestamp asc

// Slow or failing dependencies (SQL, HTTP, Service Bus)
dependencies
| where timestamp > ago(24h) and success == false
| summarize count() by target, type

Note: in a workspace-based resource queried from the Log Analytics workspace, the table names are AppRequests, AppDependencies, AppExceptions, AppTraces, and so on, with different column names (TimeGenerated, DurationMs). Queries from the Application Insights Logs blade use the classic names shown above.

7. Monitoring features worth using

• Live Metrics: near-real-time view of requests, failures, and CPU while you deploy or debug.
• Application Map: dependency graph with failure and latency per link.
• Failures and Performance blades: group exceptions and slow operations, then drill into samples.
• Availability tests: synthetic checks from multiple regions (URL ping is being replaced by Standard tests; use those).
• Smart detection: automatic anomaly alerts for failure-rate and latency changes.
• Workbooks and dashboards: shared visual reports built from KQL.
• Profiler and Snapshot Debugger: deeper diagnostics for slow code and exceptions (primarily classic SDK and App Service scenarios).

8. Alerts

Create alert rules on metrics or on KQL queries, and route them to an action group (email, SMS, Teams, webhook, Logic App).

az monitor metrics alert create \
  --name high-failed-requests --resource-group my-rg \
  --scopes <app-insights-resource-id> \
  --condition "count requests/failed > 10" \
  --window-size 5m --evaluation-frequency 1m \
  --action <action-group-id>

Typical alerts: failed request spike, p95 latency above a threshold, exceptions rate, availability test failures, and dependency failures. Prefer log-based alerts for business conditions (e.g., no orders in 30 minutes).

9. Cost and data control

Application Insights is billed by data ingested (GB) to the workspace, so control volume deliberately:

• Sampling: adaptive sampling is on by default in the classic SDK. With OpenTelemetry, set SamplingRatio (or use rate-limited sampling) in AzureMonitorOptions. Sampled telemetry keeps correlated items together.
• Log levels: don't send Debug or Trace in production, and raise noisy framework categories to Warning.
• Daily cap: set a cap on the workspace as a safety net, knowing that data stops being collected when it's hit.
• Retention: default is 90 days for Application Insights tables (30 days for Log Analytics generally); extend or archive only what you need.
• Filter noise: drop health-check requests and static files with a processor or filter.

10. Security and good practice

• Never log secrets or PII (tokens, passwords, card numbers, full emails). Sanitize or omit them before logging.
• Use Entra authentication for ingestion (DefaultAzureCredential with the Monitoring Metrics Publisher role) and disable local (key-based) auth, so a leaked connection string can't be used to inject fake telemetry.
• Use RBAC to control who can read logs (Log Analytics Reader, Monitoring Reader).
• Use private link (Azure Monitor Private Link Scope) if telemetry must stay off the public internet.
• Use consistent correlation: include IDs such as OrderId in log templates so you can search by business key.
• One resource per environment (or at least distinct cloud role names and an environment dimension) so dev noise doesn't pollute production.

Enabling Entra ingestion with the OpenTelemetry distro:

builder.Services.AddOpenTelemetry().UseAzureMonitor(o =>
{
    o.Credential = new DefaultAzureCredential();
});

Minimal setup checklist

1. Create a workspace-based Application Insights resource.
2. Add Azure.Monitor.OpenTelemetry.AspNetCore and call UseAzureMonitor(), with the connection string in app settings.
3. Log through ILogger with structured templates.
4. Set the service name so Application Map shows each service.
5. Add alerts for failures, latency, and availability.
6. Configure sampling, log levels, and a daily cap to control cost.

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