OpenCode Configuration in VSCode

In this blog I will show you ow to Set Up OpenCode and Microsoft Power Platform Skills 100% Free in VS Code

Are you looking for a fully autonomous AI coding assistant that can create files, modify your existing codebase, and build custom Microsoft Power Platform components—all without paying a single penny in subscription fees or API costs?

By combining OpenCode (a powerful, open-source AI agent framework) with premium free cloud models or local offline models, you can build an enterprise-ready development environment directly inside Visual Studio Code.

Here is a complete, step-by-step walkthrough to get this entire ecosystem running on a Windows machine for zero cost.


🏗️ Core Architecture: How This Runs Fully Free

To bypass pay-as-you-go credits and monthly fees, this setup relies on a smart dual layout:

  1. The Software Framework: OpenCode’s engine, command-line interface (CLI) tools, and native VS Code UI components are completely free and open-source under the MIT License.
  2. The AI Compute: The workflow connects to premium, cloud-hosted free tiers (like MiniMax M3.1 Flash Free or DeepSeek V4.1 Flash Free) provided natively in the client. Alternatively, it can link to local hardware via Ollama to process data completely offline.

🛠️ Step 1: Install the OpenCode VS Code Extension

First, let’s provision the graphical interface:

  1. Launch Visual Studio Code.
  2. Advance to the Extensions Marketplace tab via the left navigation dock (or strike Ctrl + Shift + X).
  3. Query for “OpenCode” and trigger the installation pipeline.

🛠️ Step 2: Establish the Global Terminal Engine

Because modern node installations running natively on Windows can trigger automated script blocks, you must pre-authorize global setup safety flags to prevent directory compilation errors like ENOENT / package.json missing.

  1. Open a terminal prompt window inside VS Code (powershell) or launch a Windows Command Prompt.
  2. Run this configuration string to link execution authorization directly to your global user profile configuration:bash
  3. npm config set allow-scripts=@opencode/cli --location=user Use code with caution.
  4. Execute the global installation command using the consolidated package identifier (make sure it’s written as one word without a hyphen):bash
  5. npm install -g @opencode/cli Use code with caution.
  6. Verify deployment integrity by checking the core system versioning signature:bash
  7. opencode --version Use code with caution.
  8. Restart Visual Studio Code completely so your active editors re-index and latch onto the clean global path.

🛠️ Step 3: Invoke the OpenCode Chat Interface

Once VS Code restarts, you need to bring up the AI panel to begin interacting with the agent:

  1. Via the Activity Bar: Click on the dedicated OpenCode logo icon on the far-left sidebar menu of VS Code.
  2. Via the Command Palette: Alternatively, press Ctrl + Shift + P, type OpenCode: Focus on Chat View, and press Enter.
  3. This action instantiates the active sidebar or editor panel where your conversations take place.

🛠️ Step 4: Wire the Free AI Brain Picker

Now, configure the specific AI brain that will process your instructions:

  1. Bring focus into your active OpenCode chat window panel.
  2. Trigger the runtime selector configuration menu by using the keyboard shortcut:
    • Ctrl + A
  3. Use your keyboard’s Up/Down arrow keys to slide past the premium billable profiles down to the free tiers.
  4. Hover over your chosen free cloud engine option—gemma4:31b:cloud—and strike Enter to register your configuration choice. Type /model from the opencode chat window

💡 Local Setup Tip: Notice that OpenCode automatically senses any local network compilation nodes (like llama3:latest or granite3.2-vision:latest) at the absolute foot of this identical selection panel. If you install Ollama, you can toggle down to these profiles to construct code completely offline using your own computer’s graphics compute!


🛠️ Step 5: Inject the Microsoft Power Platform Plugin Suite

To bridge the gap between OpenCode and Microsoft’s enterprise business services, deploy the complete, official Power Platform ecosystem files directly into your active folder topology.

https://github.com/microsoft/power-platform-skills/tree/main

  1. Paste this comprehensive automated installation script block directly inside your active workspace terminal (C:\Dev\DemoPowerPlatformskills):powershell
  2. iwr https://githubusercontent.com -OutFile install.js; node install.js --include-dataverse; del install.js Use code with caution.
  3. The runtime execution will securely download the entire verified enterprise plugin directory layer straight into your system’s global tracking directory (~\.agents\skills\):
    • Target Deployments: power-pages, model-apps, pcf, canvas-apps, power-automate, process-intelligence, and dataverse.

🛠️ Step 6: Execute Commands and Code Autonomously

OpenCode maps commands inside your project file trees utilizing context tracking and natural language instead of traditional raw syntax handlers.

  1. Verify Module Status: Type /skills in the main panel prompt to cause the system to refresh its local tracking registry. Your tools (like microsoft-docs, kql, and cloud-solution-architect) will show up immediately.
  2. Call Tools via Dialogue: To build files, simply declare your target directory goals through ordinary sentences:
    • Example Intent: "Use the @pcf skill to scaffold a field control named CustomInput inside a new directory at the root called /my-new-pcf-components"
  3. Approve File Creation: OpenCode is incredibly clever. If a workspace-specific sub-plugin isn’t linked to your current directory folder yet, it will auto-detect it, download the local manifest on the fly, and structure it cleanly inside your active file tree view under plugins/pcf/.
  4. You are Always in Control: OpenCode will spin up a local folder blueprint map, stage the source files (ControlManifest.Input.xml, index.ts), and wait for you to click the visual Approve button in the chat pane before modifying your disk configuration. If you ever make a mistake, simply type /undo to roll it back.

🎉 Conclusion

You are now fully configured and ready to go! You have a powerful, context-aware coding agent attached to Microsoft’s official development guidelines, completely optimized to run on zero budget.

Posted in ai | Tagged , , , , , , | 2 Comments

Microsoft Agent Framework Console App to a Visual DevUI Playground

In our previous blog post, we explored how to get up and running with the Microsoft Agent Framework (MAF) by building a lightweight C# Console Application. We successfully wired up local C# tools to a local Ollama LLM (qwen2.5:7b), sent a mathematical prompt via code, and printed the result back out to the command line.

While console scripts are fantastic for verifying that your LLM connectivity works, they fall short when you want to interactively test your agent, experiment with different prompts on the fly, or peek inside the model’s chain-of-thought.

To solve this, Microsoft built DevUI directly into the framework. It acts like a local “Swagger UI” for AI, providing a graphical chat dashboard and a real-time visual trace execution graph.

This post covers exactly what to install, how to update your project setup, and how to run your first interactive agent test in DevUI.


Step 1: What to Install (Updating Packages & Project Type)

Because DevUI functions as an interactive web dashboard, it needs to be hosted on a local web server (Kestrel) rather than running as a one-shot terminal command.

First, open your MAFSimpleToolDemo.csproj file and swap your core project type by changing the very first line from Sdk="Microsoft.NET.Sdk" to Sdk="Microsoft.NET.Sdk.Web".

Next, add the two essential hosting and UI packages to your project:

bash

dotnet add package Microsoft.Agents.AI.Hosting --prerelease
dotnet add package Microsoft.Agents.AI.DevUI --prerelease

Use code with caution.

Your updated .csproj configuration should look like this:

xml

<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Agents.AI" Version="1.22.0" />
<PackageReference Include="Microsoft.Agents.AI.DevUI" Version="1.22.0-preview.260918.1" />
<PackageReference Include="Microsoft.Agents.AI.Hosting" Version="1.22.0-preview.260918.1" />
<PackageReference Include="Microsoft.Extensions.AI" Version="10.10.0" />
<PackageReference Include="OllamaSharp" Version="5.4.30" />
</ItemGroup>
</Project>

Use code with caution.


Step 2: The Core Code Transformation

Next, open your Program.cs. Instead of instantly executing agent.RunAsync() in a procedural loop, we wrap our existing Ollama client configuration, custom math tools (CalculatorTools), and agent parameters into a standard .NET WebApplication builder.

Replace your Program.cs code with this structure:

csharp

using MAFSimpleToolDemo.Tools;
using Microsoft.Agents.AI;
using Microsoft.Agents.AI.DevUI;
using Microsoft.Agents.AI.Hosting;
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.AI;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.DependencyInjection;
using OllamaSharp;
var builder = WebApplication.CreateBuilder(args);
// 1. Setup your external tools as before
var calculator = new CalculatorTools();
var addTool = AIFunctionFactory.Create(calculator.Add);
var subtractTool = AIFunctionFactory.Create(calculator.Subtract);
// 2. Define your agent metadata and options
var agentOptions = new ChatClientAgentOptions
{
Name = "CalculatorAgent", // DevUI will discover this exact name!
ChatOptions = new ChatOptions
{
AllowMultipleToolCalls = false,
Tools = [addTool, subtractTool],
Instructions = """
You are a calculator assistant.
You have access to two tools:
1. Add
2. Subtract
When the user asks you to perform a calculation, you MUST use the appropriate tool.
Do not calculate the answer yourself.
"""
}
};
// 1. Add your model to Dependency Injection
builder.Services.AddSingleton<IChatClient>(sp =>
new OllamaApiClient(new Uri("http://localhost:11434"), "qwen2.5:7b"));
// 2. Register the agent matching the framework's hosting method signature
builder.AddAIAgent("CalculatorAgent", (sp, agentName) =>
{
// Resolve the Ollama chat client from the container
var chatClient = sp.GetRequiredService<IChatClient>();
// Return a new ChatClientAgent instance using your custom options
return new ChatClientAgent(chatClient, agentOptions);
});
// 5. Register required DevUI and background OpenAI-compatible messaging channels
builder.AddDevUI();
builder.AddOpenAIResponses();
builder.AddOpenAIConversations();
var app = builder.Build();
// 6. Map the core API endpoints required for the frontend to talk to the backend
app.MapOpenAIResponses();
app.MapOpenAIConversations();
app.MapDevUI(); // Accessible at http://localhost:<port>/devui
Console.WriteLine("DevUI Server is spinning up...");
app.Urls.Add("http://localhost:5000");
app.Run();

Use code with caution.


Step 3: Triggering the Development Flag

DevUI contains strict security safeguards to prevent its diagnostic screens from accidentally executing in Live/Production cloud systems. Because a command-line environment defaults to Production, we must instruct the system that it is running a local debug session.

To do this seamlessly without hacking environment commands into your console, create a folder named Properties in the root of your project, and inside it, create a file called launchSettings.json:

json

{
"profiles": {
"http": {
"commandName": "Project",
"launchBrowser": true,
"launchUrl": "devui",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
},
"applicationUrl": "http://localhost:5000"
}
}
}

Use code with caution.


Step 4: Testing Your Agent via DevUI

Now you are ready to witness your agentic system in action!

  1. In your project directory terminal, type: dotnet run
  2. Your console logs will spin up, showing that Kestrel is now actively running a background engine on port 5000 and the hosting environment has switched to Development.
  3. Open your browser and navigate directly to: http://localhost:5000/devui

The Testing Workflow

  • Select the Agent Profile: At the top right of the dashboard, you will find a drop-down selector panel containing your CalculatorAgent.
  • Submit an Interactive Chat Prompt: Type a compound math problem into the message window, such as: “Add 25 and 15 then subtract 10 from the result.” and press enter.
  • Inspect the Visual Trace Graph: Instead of waiting for a single text response, you will instantly watch DevUI map out the model’s inner reasoning path. You can click on the individual nodes to view the raw JSON payloads passed into your C# Add and Subtract methods, check what token metrics were consumed, and see the exact step-by-step logic your agent used to come up with the correct response.

DevUI is built purely to make testing and debugging AI agents as easy as possible.

Instead of writing custom frontend chat screens just to test your ideas, or digging through messy terminal outputs to figure out why an agent failed, DevUI gives you an instant web dashboard out of the box.

It acts as your agent’s flight data recorder and testing sandbox, allowing you to:

  • 💬 Chat with your agent interactively without coding a single button or UI element yourself.
  • 🕵️‍♂️ Inspect tool parameters to see the exact JSON inputs the LLM extracted and sent to your C# functions.
  • 🪵 Watch the Chain-of-Thought layer by layer to see why an agent chose a specific tool or action.
  • 🔄 Test prompt updates instantly by altering system instructions and hitting refresh to see how the agent responds differently.


Posted in Uncategorized | Tagged , , , , , , , , , , , , | Leave a comment

Building a Multi-Tool Agentic Workflow with Microsoft Agent Framework (MAF) and Ollama

If you are new to Agentic AI, one of the easiest ways to understand how an AI agent works is to build a very small example.

This time, I am going to create two functions and giving the agent two tools:

Add()
Subtract()

The interesting part is that we don’t write if/else statements to decide which function to call.

Instead, the AI agent decides which tool is appropriate based on the user’s request.

For example:

What is 25 + 17?

The agent can choose the Add tool.

Or:

What is 25 – 17?

The agent can choose the Subtract tool.

Or:

Add 25 and 17, then subtract 10 from the result.

The agent can add then subtract.

This gives us a simple introduction to a multi-tool agentic workflow.

What We Are Building

Our application will look like this:

                 User
                   │
                   ▼
              AI Agent
                   │
          ┌────────┴────────┐
          │                 │
          ▼                 ▼
       Add Tool        Subtract Tool
          │                 │
          ▼                 ▼
        Result            Result

The important point is that the agent has access to both tools, but it only needs to use the tool required for the user’s request.

Prerequisites

For this example, I used:

  • Visual Studio
  • .NET 10
  • Ollama
  • Llama 3.2
  • Microsoft Agent Framework
  • Microsoft.Extensions.AI
  • OllamaSharp

The AI model runs locally using Ollama, so no OpenAI API key is required.

Step 1 — Install Ollama

First, install Ollama on your computer.

After installation, open a terminal and check that Ollama is available:

ollama --version

Then download the model we are going to use:

ollama pull llama3.2

You can check the installed models with:

ollama list

You should see llama3.2 in the list.

Ollama normally runs its local API on:

http://localhost:11434

Step 2 — Create a Visual Studio Project

Open Visual Studio and create a new:

Console App

I called my project:

MAFSimpleToolDemo

The project targets:

.NET 10

Step 3 — Install Microsoft Agent Framework

Open:

Tools → NuGet Package Manager → Manage NuGet Packages for Solution

Search for:

Microsoft.Agents.AI

Install:

Microsoft.Agents.AI

For this example, I used version:

1.21.0

This package provides the Microsoft Agent Framework functionality we need.

Step 4 — Install Microsoft.Extensions.AI

Next install:

Microsoft.Extensions.AI

This provides common AI abstractions used by the application and the agent.

Step 5 — Install OllamaSharp

For the local Ollama connection, install:

OllamaSharp

OllamaSharp provides .NET support for communicating with Ollama and gives us an IChatClient implementation that can be used with Microsoft Agent Framework.

There are different AI model providers and runtimes that can be used with Microsoft Agent Framework, such as OpenAI, Google models such as Gemma, and local runtimes such as Ollama. In this example, I am using Ollama to run the AI model locally on my machine.

Step 6 — Create the Tools Folder

In your Visual Studio project, create a folder called:

Tools

Inside this folder create:

CalculatorTools.cs

Our calculator will contain two tools:

Add()
Subtract()

Step 7 — Create the Add and Subtract Tools

Add the following code to CalculatorTools.cs:

using System.ComponentModel;
namespace MAFSimpleToolDemo.Tools
{
public class CalculatorTools
{
[Description("Adds two numbers together.")]
public double Add(
[Description("The first number to add")] double a,
[Description("The second number to add")] double b)
{
Console.WriteLine($"[TOOL] Add called with {a} + {b}");
double result = a + b;
return result;
}
[Description("Subtracts the second number from the first number.")]
public double Subtract(
[Description("The number to subtract from")] double a,
[Description("The number to subtract")] double b)
{
Console.WriteLine($"[TOOL] Subtract called with {a} - {b}");
double result = a - b;
return result;
}
}
}

These are simply normal C# methods.

For example:

Add(25, 17)

returns:

42

And:

Subtract(25, 17)

returns:

8

The [Description] attributes are important because they help describe the tools to the AI agent.

Step 8 — Create the AI Tools

Now open:

Program.cs

First import the required namespaces:

using Microsoft.Agents.AI;
using Microsoft.Extensions.AI;
using MAFSimpleToolDemo.Tools;
using OllamaSharp;

Create the calculator:

var calculator = new CalculatorTools();

Now convert our C# methods into AI tools:

var addTool = AIFunctionFactory.Create(
calculator.Add);
var subtractTool = AIFunctionFactory.Create(
calculator.Subtract);

We now have two AI tools:

addTool
subtractTool

Step 9 — Connect to Ollama

Create the Ollama client:

var chatClient = new OllamaApiClient(
new Uri("http://localhost:11434"),
"llama3.2");

This connects our application to the locally running Ollama server.

We are now using:

C# Application
↓
OllamaSharp
↓
Ollama
↓
Llama 3.2

Step 10 — Create the Agent

Now we create the Microsoft Agent Framework agent.

var agent = chatClient.AsAIAgent(
name: "CalculatorAgent",
instructions:
"""
You are a calculator assistant.
You have access to two tools:
1. Add
Use this to add two numbers.
2. Subtract
Use this to subtract the second number from the first number.
When the user asks you to perform a calculation,
you MUST use the appropriate tool.
If the user asks you to perform multiple calculations,
complete them in the order requested.
If the result of one calculation is needed
for the next calculation, use the actual result
returned by the previous tool call.
Do not calculate the answer yourself.
""",
tools:
[
addTool,
subtractTool
]);

This is where the agent gets its capabilities.

We are effectively telling the agent:

You have an Add tool and a Subtract tool. Choose the appropriate one for the user’s request.

Step 11 — Ask the Agent a Question

Now we can ask the agent a question:

var response = await agent.RunAsync(
"What is 25 + 17?");

The agent receives the question.

The AI can decide:

I need addition.

and select:

Add(25, 17)

The C# method then executes and returns 42

Step 12 — Test Subtraction

We can also ask:

var response = await agent.RunAsync(
"What is 25 - 17?");

The agent can select:

Subtract(25, 17)

and the C# method returns: 8

Step 13 — Test Multiple Tool Calls

Now we can try something more interesting:

var response = await agent.RunAsync(
"Add 25 and 17, then subtract 10 from the result.");

Conceptually, we want the workflow to look like:

Add(25, 17)
↓
42
↓
Subtract(42, 10)
↓
32

This demonstrates the idea of sequential tool calls.

The result from the first operation can become information used by the agent when making the next tool call.

Step 14 — Complete Code

using Microsoft.Agents.AI;
using Microsoft.Extensions.AI;
using MAFSimpleToolDemo.Tools;
using OllamaSharp;
var calculator = new CalculatorTools();
// Convert our C# method into an AI tool
var addTool = AIFunctionFactory.Create(calculator.Add);
var subtractTool = AIFunctionFactory.Create(calculator.Subtract);
Console.WriteLine("Tool created successfully!");
var chatClient = new OllamaApiClient(
new Uri("http://localhost:11434"),
"qwen2.5:7b");
Console.WriteLine("Ollama client created successfully!");
// Define options cleanly and ensure ChatOptions are fully populated
var agentOptions = new ChatClientAgentOptions
{
Name = "CalculatorAgent",
ChatOptions = new ChatOptions
{
AllowMultipleToolCalls = false, // Forces Qwen to wait after the first tool execution
Tools = [addTool, subtractTool], // Pass the tools directly inside ChatOptions
Instructions = """
You are a calculator assistant.
You have access to two tools:
1. Add
Use this to add two numbers.
2. Subtract
Use this to subtract the second number from the first number.
When the user asks you to perform a calculation,
you MUST use the appropriate tool.
If the user asks you to perform multiple calculations,
complete them in the order requested.
If the result of one calculation is needed
for the next calculation, use the actual result
returned by the previous tool call.
Do not calculate the answer yourself.
"""
}
};
// Pass the fully configured agentOptions object directly
var agent = chatClient.AsAIAgent(agentOptions);
Console.WriteLine("MAF agent created successfully!");
var response = await agent.RunAsync(
"Add 25 and 15 then subtract 10 from the result.");
Console.WriteLine();
Console.WriteLine("========== AGENT RESPONSE ==========");
Console.WriteLine(response.Text);
Console.WriteLine("====================================");

Tools directory > CalculatorTools.cs

using System.ComponentModel;
namespace MAFSimpleToolDemo.Tools
{
public class CalculatorTools
{
[Description("Adds two numbers together.")]
public double Add(
[Description("The first number to add")] double a,
[Description("The second number to add")] double b)
{
Console.WriteLine($"[TOOL] Add called with {a} + {b}");
return a + b;
}
[Description("Subtracts the second number from the first number.")]
public double Subtract(
[Description("The number to subtract from")] double a,
[Description("The number to subtract")] double b)
{
Console.WriteLine($"[TOOL] Subtract called with {a} - {b}");
return a - b;
}
}
}

Step 15 — Run the Application

Before you run the application make sure Ollama qwen2.5:7b is running.

Here I am running qwen model locally

In Visual Studio, press:

Ctrl + F5

or:

Debug → Start Without Debugging

Finally, display the agent’s response:

What Have We Built?

We now have a small multi-tool AI agent.

                 ┌───────────────┐
                 │   User        │
                 └───────┬───────┘
                         │
                         ▼
                 ┌───────────────┐
                 │  MAF Agent    │
                 └───────┬───────┘
                         │
               Decides which tool
                   is required
                         │
                ┌────────┴────────┐
                ▼                 ▼
           ┌─────────┐       ┌───────────┐
           │  Add    │       │ Subtract  │
           └────┬────┘       └─────┬─────┘
                │                  │
                ▼                  ▼
             C# code            C# code

The application provides the tools.

The AI decides which tool to use.

The C# code performs the actual operation.

Why Is This Agentic?

This is different from traditional application logic.

A traditional application might do something like:

If question contains "+"
call Add()
If question contains "-"
call Subtract()

With an agent, we instead give the AI the available tools:

Add
Subtract

and describe what they do.

The agent can then decide which tool is appropriate based on the user’s request.

Conclusion

This same pattern can be used for much more useful business operations.

This simple example is a good introduction to how Microsoft Agent Framework (MAF) can work with multiple tools. Instead of writing traditional if/else logic to decide which C# method should run, we give the AI agent a set of tools and allow it to select the appropriate tool based on the user’s request.

We started with a simple Add() function and then introduced Subtract(). This demonstrates the basic idea of a multi-tool agentic workflow, where an agent can choose different capabilities depending on what the user asks.

The same approach can be applied to real-world applications. Instead of calculator functions, our tools could perform tasks such as checking a customer, validating a verification code, creating a support ticket, or retrieving ticket information.

The calculator may be a small example, but it provides a useful foundation for understanding how AI models, MAF, and C# tools work together to create agentic applications.

Posted in Uncategorized | Tagged , , , , , , , , , , , , | 2 Comments

Demystifying Agentic AI: A Beginner’s Guide to AI Workflows

If you’ve been following Artificial Intelligence recently, you’ve probably heard terms like AI Agents, Agentic AI, AI Workflows, LangChain, LangGraph, CrewAI, and Microsoft Agent Framework.

For someone just starting out, it can all sound rather complicated.

So let’s forget the jargon for a moment.

In this series, I’m going to explain Agentic AI in plain English, using simple diagrams and a real-world example from one of my own projects: automatically reading handwritten badminton scoresheets.

You don’t need to be an AI expert to follow along.


🤖 First Things First: What Is Agentic AI?

Imagine you give an AI this instruction:

“Read this handwritten badminton scoresheet and save the result.”

A normal AI model might look at the image and tell you what it sees.

But an agentic AI system can potentially do much more.

It can:

  • Understand the goal
  • Decide what needs to happen
  • Use tools such as APIs or databases
  • Check the result
  • Decide what to do next
  • Try again if something went wrong
  • Ask a human for help when necessary

The important idea is this:

An AI model generates an answer. An agentic system can use that model as part of a process that takes actions and makes decisions.

That distinction is important.


🧠 AI Model vs AI Workflow vs Agentic Workflow

Let’s make this really simple.

Imagine we have a photograph of a scoresheet.

1️⃣ AI Model

The simplest version is:

📷 Image
↓
🤖 AI Model
↓
📝 Answer

The AI looks at the image and produces an answer.

The model could be ChatGPT, Claude, Gemini, or a local vision model (qwen) running on your own computer.


2️⃣ AI Workflow

Now we connect several steps together:

📷 Image
↓
🤖 Extract information
↓
✅ Validate information
↓
💾 Save to database

This is a workflow.

The developer has defined the sequence.

Step 1 happens, then Step 2, then Step 3.


3️⃣ Agentic Workflow

Now imagine the AI discovers that something doesn’t look right.

Instead of simply stopping, the system can make a decision:

📷 Image
↓
🤖 Extract information
↓
✅ Validate
↓
Is it correct?
↙ ↘
YES NO
↓ ↓
💾 Save 🔄 Retry
↓
🤖 Extract again

This is where things start becoming agentic.

The system isn’t necessarily following one fixed path anymore.

It can make decisions during the process.


🧱 So What Is an Agentic AI Framework?

This is where names such as LangChain, LangGraph, CrewAI and Microsoft Agent Framework come into the picture.

Think of the AI model as an engine.

The engine itself is powerful, but an engine isn’t a complete car.

You also need things such as:

  • Steering
  • Controls
  • Wheels
  • Sensors
  • Brakes
  • A way of deciding where to go

An agentic AI framework provides software building blocks that help you build the system around the AI model.

A simplified view is:

             🤖 AI MODEL
             "The engine"
                  +
          🧱 AI FRAMEWORK
     "The software around it"
                  ↓
        ┌───────────────────┐
        │       TOOLS       │
        │ APIs / databases  │
        ├───────────────────┤
        │       STATE       │
        │ What happened?    │
        ├───────────────────┤
        │     WORKFLOW      │
        │ What happens next?│
        ├───────────────────┤
        │    DECISIONS      │
        │ Which path?       │
        └───────────────────┘

The framework doesn’t magically make an AI intelligent.

Instead, it gives developers reusable building blocks for constructing more sophisticated AI applications.


🧠 What Does an Agent Actually Do?

There isn’t one universal definition of an AI agent.

But many agentic systems involve some combination of these capabilities.

1. Understand the goal

The system receives a task.

“Process this scoresheet.”

2. Reason about what needs to happen

It determines what information it needs and what actions are required.

3. Use tools

The AI might interact with:

  • A database
  • An API
  • A web service
  • Files
  • Python code
  • Business applications

4. Maintain state

The system remembers what has happened during the workflow.

For example:

State
Image received ✓
Data extracted ✓
Validation ✗
Error "Player 4 score missing"
Retry count 1

You can think of state as the workflow’s memory of what has happened so far.

5. Make decisions

For example:

“The extracted data doesn’t look right, so I should try again.”

6. Iterate

The system can repeat a step when necessary.

That last capability is particularly useful when working with AI because AI output isn’t always perfect.


🗺️ Five Common AI Workflow Patterns

Rather than thinking of these as five different types of AI frameworks, it’s easier to think of them as common ways of designing AI systems.

1️⃣ Sequential Workflow — The Assembly Line

This is the simplest.

A → B → C → D

For example:

📄 Document
↓
🤖 Extract
↓
🔍 Analyse
↓
📝 Create report

Every step follows the previous one.

This is useful when the process is predictable.


2️⃣ Conditional Workflow — Choose a Path

Sometimes the next step depends on the result.

             🔍 Check
                ↓
             Is it OK?
             ↙      ↘
           YES       NO
            ↓         ↓
          Save      Review

For example:

If the customer’s request is straightforward, answer automatically.

If it requires approval, send it to a human.


3️⃣ Loop / Retry Workflow — Try Again

This is particularly useful with AI.

🤖 Generate
↓
🔍 Validate
↓
Correct?
↙ ↘
YES NO
↓ ↓
Done 🔄 Retry
↓
🤖 Generate

You can also put a limit on the number of attempts.

For example:

Try a maximum of three times, then send the task to a human.


4️⃣ Human-in-the-Loop

AI doesn’t always need to make the final decision.

Sometimes the best design is:

🤖 AI
↓
🔍 Check
↓
👤 Human approval
↓
💾 Continue

This can be particularly important for business processes where an incorrect decision could have serious consequences.

The AI can do the repetitive work while a person remains responsible for the final approval.


5️⃣ Multi-Agent Workflow

Sometimes several AI agents can be given different responsibilities.

For example:

             🎯 Task
                ↓
        ┌───────┼───────┐
        ↓       ↓       ↓
    🔎 Research ✍️ Writer 🔍 Reviewer
        │       │       │
        └───────┼───────┘
                ↓
             📄 Result

One agent might research.

Another might write.

Another might review the result.

Frameworks such as CrewAI are designed around this type of agent collaboration.


🧰 Where Do the Popular Frameworks Fit?

Now we can put some of the popular frameworks into context.

FrameworkIn simple terms
LangChainBuilding blocks for applications using LLMs, tools and workflows
LangGraphBuilding stateful workflows with branching, loops and more complex control
CrewAIBuilding systems where agents can have different roles and collaborate
Microsoft Agent FrameworkMicrosoft’s framework for building agents and orchestrated workflows
LangFlow / FlowiseVisual tools for assembling AI workflows

One important point:

These aren’t five completely separate categories.

A framework can support several different patterns.

For example, LangGraph can be used to build a simple workflow, but it becomes particularly interesting when you need state, branching, loops, persistence or human intervention.


🔵 A Quick Look at Microsoft Agent Framework

If you’ve been reading older Microsoft AI tutorials, you may have come across:

  • Semantic Kernel
  • AutoGen

Microsoft has been bringing these capabilities together under the Microsoft Agent Framework.

The idea is to provide developers with a unified framework for building agents and workflows, combining concepts from the earlier Microsoft agent technologies with additional orchestration capabilities.

So if you’re starting a new Microsoft-focused project, it’s worth looking at the current Microsoft Agent Framework documentation rather than automatically following older AutoGen or Semantic Kernel tutorials.


🏸 My Real-World Example: A Badminton Scoresheet

Now let’s move away from theory.

I regularly play badminton, and after a match I receive a photograph of the completed scoresheet.

Often, that scoresheet arrives as an image through WhatsApp.

My existing application can produce the final scorecard PDFs, but traditionally there was one manual step:

I had to read the scoresheet and enter the information into the application.

So I started thinking:

Could AI read the scoresheet for me?

The answer is yes.

But there’s a catch.

Handwriting isn’t always easy for AI to understand.

The photograph might be:

  • Slightly blurred
  • At an angle
  • Poorly lit
  • Handwritten
  • Missing information
  • Difficult to read

And even when the AI produces something that looks correct, we still need to check it.

That gives us a perfect example of why an agentic workflow can be useful.


📷 Step 1 — The Simple Version

The first thing we might build is:

📷 Scoresheet
↓
🤖 Vision AI
↓
📋 JSON

For example:

{
"match_date": "08/09/2026",
"home_team": "Oakgate",
"away_team": "Accy Flyers"
}

That’s already useful.

But what happens if the AI gets something wrong?


⚠️ What If the AI Makes a Mistake?

Imagine the AI reads the scoresheet and produces:

Game 1 ✓
Game 2 ✓
Game 3 ✓
Game 4 ✗
Game 5 ✓

The fourth game is missing.

If our program simply saves the result, we’ve just put incorrect information into our database.

A simple workflow might do this:

📷 Image
↓
🤖 AI
↓
💾 Database

There is no opportunity to correct the mistake.


🔄 Let’s Make It More Intelligent

Instead, we can introduce a validation step.

📷 Image
↓
🤖 Extract information
↓
🔍 Validate
↓
Is the information correct?
↙ ↘
YES NO
↓ ↓
💾 Save 🔄 Retry
↓
🤖 AI

Now we have something much more interesting.

The workflow can react to the result.


🧠 Where LangGraph Comes In

This is the type of problem where a graph-based workflow can be useful.

Instead of thinking of our program as:

Step 1 → Step 2 → Step 3 → Step 4

we can think about it as:

                 ┌─────────────┐
                 │ Extract     │
                 └──────┬──────┘
                        ↓
                 ┌─────────────┐
                 │ Validate    │
                 └──────┬──────┘
                        ↓
                    Correct?
                   ↙       ↘
                 YES        NO
                  ↓          ↓
               Save       Retry
                             │
                             └──────→ Extract

The important concept here is the graph.

Each box represents a piece of work.

The connections between the boxes determine where the workflow goes next.


🗃️ And What About State?

This is another important concept.

During the process, we might have:

State
─────────────────────────
Image ✓
Extracted JSON ✓
Validation result ✗
Validation error ✓
Retry count 1

When the workflow loops back to the extraction step, it can use that information.

For example:

“The previous extraction missed one game. Please carefully inspect the scoresheet again and make sure all games are included.”

The AI gets another opportunity to produce a better result.

We can also tell the system:

Maximum retries = 3

If it still can’t produce a valid result, stop and ask a human to check it.


🚦 Why This Is Different From Ordinary Automation

This distinction is worth remembering.

A traditional automation might be:

WHEN something happens
↓
DO A
↓
DO B
↓
DO C

An agentic workflow might be:

WHEN something happens
↓
DO A
↓
CHECK RESULT
↓
WHAT SHOULD HAPPEN NEXT?
↙ ↘
PATH A PATH B
↓ ↓
Continue Retry

The workflow has decision points.

That’s one of the characteristics that makes agentic systems interesting.


💡 Do You Actually Need an AI Agent?

Here’s something that is often overlooked.

No.

Not every AI application needs to be agentic.

If your problem can be solved reliably with:

Python
+
API call
+
if/else
+
database

then that’s perfectly fine.

You don’t need to add an agent framework just because the word “agent” is fashionable.

Agentic approaches become particularly useful when your process involves things such as:

  • Uncertainty
  • Dynamic decisions
  • Tool selection
  • Multiple steps
  • Retries
  • Human interaction
  • Changing paths
  • Complex orchestration

The goal should always be:

Use the simplest architecture that reliably solves the problem.


🎯 So What Have We Learned?

Let’s bring everything together.

An AI model

🤖 Generates an answer

An AI workflow

A → B → C

An agentic workflow

A → B → Decide → C
↘
Retry

An agentic framework

Provides the software building blocks that help us create these systems.

And frameworks such as LangChain, LangGraph, CrewAI and Microsoft Agent Framework provide different approaches and abstractions for building them.


🏸 And Our Badminton Example?

We started with:

📷 Scoresheet
↓
🤖 AI
↓
📋 JSON

Then we evolved it into:

📷 Scoresheet
↓
🤖 AI extraction
↓
🔍 Validation
↓
Correct?
↙ ↘
YES NO
↓ ↓
💾 Save 🔄 Retry

That’s the important transition.

We’re no longer simply asking AI a question.

We’re building a system around the AI that can inspect results, make decisions and take the next action.

Check out my blog and video which explains how to extract score sheet data using agentic ai.

Thanks for reading my blog

Posted in Uncategorized | Tagged , , , , , , , , , , , , , , | Leave a comment

From Handwritten Badminton Scorecards to Structured Data: Building an Agentic AI Workflow

Introduction

I regularly play badminton, and as part of our weekly matches, I receive handwritten match scorecards as images through WhatsApp.

At the moment, I have an app that produces a PDF containing the match statistics. However, there is a manual step involved: I have to look at the scorecard image, enter the details into the app, and then generate the PDF.

That got me thinking — what if the scorecard could be read automatically?

What I wanted to achieve

My goal was quite straightforward.

When I upload a badminton scorecard image to OneDrive, I want the system to:

  1. Read the image.
  2. Extract the teams.
  3. Extract the players.
  4. Extract all 18 game scores.
  5. Return structured JSON.
  6. Check that the extraction is complete.
  7. Automatically retry if something is missing.
  8. Convert the result into the format required by my database. (This will be implemented later)

I didn’t want to manually copy the information from every scorecard.

In this blog, I’ll show you how I built an agentic AI workflow that takes a handwritten badminton scorecard and converts it into structured JSON data.

The current workflow brings together several technologies:

  • Ollama – to run the AI models locally
  • Qwen – to analyse the handwritten scorecard and extract the information
  • Python – to build the agentic workflow and orchestrate the processing
  • Power Automate – to trigger and consume the workflow
  • ngrok – to securely expose my locally running Python API so that Power Automate can communicate with it using an HTTP action

The goal of this stage is relatively simple: take a scorecard image and produce clean, structured JSON from it.

For example, instead of manually reading something like:

Match Date: 08/09/2026
Home Team: Oakgate Rossendale A
Away Team: Accy Flyers A
Players and scores: …

the AI workflow produces a structured JSON object that can be consumed by another application or process.

This is only the beginning, though.

My longer-term plan is to connect this workflow to my database Supabase, allowing the extracted information to be stored automatically and turning the entire process into a more complete agentic workflow — from receiving the scorecard image through to storing and processing the match data.

💻 Source Code

The complete Python implementation for the agentic badminton scoresheet extraction workflow is available on GitHub.

The repository contains the FastAPI application, LangGraph workflow, Qwen Vision/Ollama integration, validation and retry logic, and compilation of the extracted scores into structured data.

👉 View the Python source code on GitHub

You can also explore the repository README for an overview of the architecture and how to run the workflow locally.

Note: The source code does not include the Power Automate trigger configuration; however, the details of how it was configured can be found below.

🎥 Watch the demo

https://www.youtube.com/watch?v=SYfcMLkJUmE&t=13s

For now, this blog focuses on the first part of that journey: how to use Ollama, Qwen, Python, Power Automate and ngrok to turn a handwritten badminton scorecard into structured JSON.

Step 1 — Set up Python

I created a small Python project for the API.

On Windows PowerShell:

mkdir scorecard-extractor
cd scorecard-extractor
python -m venv .venv
.\.venv\Scripts\Activate.ps1

If PowerShell doesn’t allow the virtual environment to activate, I can enable scripts for my user account:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Then I installed the packages required by the application:

pip install fastapi "uvicorn" langgraph requests pydantic typing-extensions

I saved the application as:

app.py

Step 2 — Install Ollama

Next I installed Ollama on Windows.

Machine requirements

For this workflow, you don’t need a high-end server. Ollama can run locally on Windows, macOS or Linux, but having a dedicated GPU makes image processing significantly faster.

For my setup, I’m using:

ComponentMy setup
OSWindows 10
CPUModern multi-core CPU
GPUNVIDIA RTX 5070
VRAM12 GB Dedicated (Shared 18 GB)
CUDA13.1
RAM32 GB recommended
OllamaLocal installation
AI modelQwen vision model
PythonPython 3.x

What do you actually need?

For a small scorecard-processing workflow, I’d suggest:

Minimum practical setup

  • 16 GB RAM
  • Modern 4–6 core CPU
  • NVIDIA GPU with 8 GB+ VRAM, if using GPU acceleration
  • Around 10–20 GB of free disk space for the model and supporting software

Recommended

  • 32 GB RAM
  • NVIDIA GPU with 12 GB+ VRAM
  • SSD
  • Windows 10/11, Linux, or macOS

The important part is VRAM, because you’re running the vision model locally through Ollama. A model such as Qwen’s vision-capable model needs enough GPU memory to load the model and process the image efficiently.

If you don’t have a suitable GPU, Ollama can also run models using the CPU, but image processing will generally be considerably slower.

For my particular implementation, I’m using an RTX 5070 with 12 GB VRAM, which gives me enough local GPU capacity to run the model without relying on a cloud AI service.

I would also make it clear in the blog that ngrok and Power Automate don’t require any special GPU hardware. The GPU requirement comes primarily from running the local AI model through Ollama.

After Ollama installation, I checked that it was available:

ollama --version

Ollama provides a local API, normally available at:

http://localhost:11434

My Python application therefore connects to:

OLLAMA_URL = "http://localhost:11434/api/generate"

The important thing here is that the AI model is running locally.

Step 3 — Install the Qwen vision model

My Python application uses the qwen2.5vl model, so I downloaded it using:

ollama pull qwen2.5vl

I can check the installed models with:

ollama list

I can also test the model directly:

ollama run qwen2.5vl

At this point I have a local AI model capable of working with images.

Step 4 — Start the FastAPI application

With my Python virtual environment activated, I start the application:

uvicorn app:app --host 0.0.0.0 --port 8000

The application is now listening on:

http://localhost:8000

FastAPI also provides an interactive documentation page:

http://127.0.0.1:8000/docs

This is useful because I can test my API before involving Power Automate.

Step 5 — Why do I need ngrok?

There is one problem.

My FastAPI application is running on my computer.

Power Automate is running in the cloud.

Power Automate cannot access my local:

localhost:8000

I therefore use ngrok to create a tunnel.

First you need to authenticate using the AuthToken

To get your auth token login to ngrok.com dashboard

Copy the AuthToken and execute the below command from your teminal

ngrok config add-authtoken yourauthtoken

After installing ngrok and configuring my account, I run:

ngrok http 8000

You will see below

Above copy https://65e1-2a10-d587-10bc-0-4128-ffb9-7650-2105.ngrok-free.app is the url you need along your method name /parse-scoresheet as a POST operation in your Power Automate http connector.

Remember whenever we run ngrok http 8000 it will change the Forwarding https://xxxx.ngrok-free.app

ngrok gives me an HTTPS address similar to:

https://example-1234.ngrok.app

A request sent to:

https://example-1234.ngrok.app

is forwarded to:

http://localhost:8000

So Power Automate can now communicate with my locally running application.

Step 6 — Connect Power Automate

Now the interesting part begins.

I created a Power Automate cloud flow.

The first step watches a OneDrive folder:

When a file is created

When I upload a scorecard, Power Automate gets the file content.

I then send the image to my FastAPI endpoint using an HTTP POST request.

The request contains the image as Base64:

{
"file_name": "match-scorecard.jpg",
"image_base64": "..."
}

The important Power Automate expression is:

base64(body('Get_file_content'))

This converts the binary image into a Base64 string that my Python API can receive.

Step 7 — The FastAPI application receives the image

My FastAPI endpoint receives the request and creates the initial LangGraph state.

Conceptually, the state looks like this:

{
"image_base64": "...",
"extracted_json": {},
"error_feedback": "",
"attempt_count": 0,
"final_tabular_data": []
}

Think of this state as the workflow’s working memory.

It keeps track of:

  • the original image
  • what the AI extracted
  • any validation errors
  • how many attempts have been made
  • the final data

Step 8 — Ask Qwen to read the scorecard

Now Qwen receives the image and instructions.

I don’t simply ask:

Read this image.

I give it a very specific structure.

For example, I tell it that I need:

Match date
Home team
Away team
Home Gents
Home Ladies
Away Gents
Away Ladies
18 individual games

I also define the expected JSON structure.

A simplified example is:

{
"match_date": "18.09.26",
"home_team": "Oakgate",
"away_team": "Accy Flyers",
"home_gents": [
"Player 1",
"Player 2",
"Player 3"
],
"home_ladies": [
"Player 4",
"Player 5",
"Player 6"
],
"away_gents": [
"Player 7",
"Player 8",
"Player 9"
],
"away_ladies": [
"Player 10",
"Player 11",
"Player 12"
],
"raw_scores": [
{
"game_number": 1,
"home_aces": 21,
"away_aces": 18
}
]
}

The actual response contains all 18 games.

Why JSON?

I don’t want a paragraph of text from the AI.

For example, this isn’t particularly useful:

Oakgate won the first game 21-18...

I want predictable data.

JSON gives me something my application can process:

{
"game_number": 1,
"home_aces": 21,
"away_aces": 18
}

That can then be inserted into a database such as Supabase.

Step 9 — Don’t trust the AI output

This is where the workflow becomes more interesting.

Suppose Qwen returns:

16 games

But I know that every scorecard should contain:

18 games

I don’t want to save that result.

Instead, my validator checks the output.

The important piece of Python is very simple:

games_found = len(
state["extracted_json"].get("raw_scores", [])
)
if games_found == 18:
return {"error_feedback": ""}

If there aren’t 18 games, I create feedback:

error_msg = (
f"Expected exactly 18 scoring rows, "
f"but Qwen extracted {games_found} blocks."
)

Now the workflow knows that the extraction failed validation.

Step 10 — Automatically retry

This is where LangGraph becomes useful.

The workflow has a decision point.

             Qwen
               ↓
           Validator
               ↓
       ┌───────┴───────┐
       ↓               ↓
    18 games       Not 18
       ↓               ↓
   Compiler         Retry
                       ↓
                      Qwen

If the extraction contains 18 games, the workflow continues.

If not, it sends the feedback back into the next Qwen attempt.

For example:

Expected exactly 18 scoring rows,
but Qwen extracted 16 blocks.

The next prompt therefore contains the correction.

Maximum of three attempts

I don’t want an infinite loop.

So I keep an attempt counter.

The workflow allows a maximum of three extraction attempts.

For example:

Attempt 1 → 16 games → FAIL
Attempt 2 → 17 games → FAIL
Attempt 3 → 18 games → SUCCESS

The workflow can then continue.

But if the third attempt still fails:

Attempt 1 → 16
Attempt 2 → 17
Attempt 3 → 16
→ STOP

The application returns an error rather than pretending the data is complete.

This is important.

The goal isn’t to make AI always say that it succeeded.

The goal is to make the application recognise when the AI hasn’t produced the data I need.

Step 11 — Compile the badminton data

Once the extraction has passed validation, I don’t ask the AI to work out the badminton player combinations.

I already know the rules.

For example, games 1 and 2 use:

Home Gent 2 + Home Gent 3
Away Gent 2 + Away Gent 3

Games 3 and 4 use the relevant ladies combination.

And so on through all 18 games.

I therefore let Python handle this part.

For example:

if g_num in (1, 2):
h_p = f"{HG[1]} / {HG[2]}"
a_p = f"{AG[1]} / {AG[2]}"

This is an important design principle in my application:

Let AI interpret the messy image, but let normal Python code handle deterministic business rules.

The AI reads the scorecard.

Python knows how the 18 games should be mapped.

The final output

After the compiler has finished, I have clean rows such as:

{
"success": true,
"attempts": 1,
"extracted_data": {
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"home_gents": [
"TREVOR MORTON",
"RAZEM",
"STEVE HUCKST"
],
"home_ladies": [
"ALISON ADAMS",
"JULIE HORSFALL",
"RACHEL"
],
"away_gents": [
"AARON",
"LIEM",
"GEORGE"
],
"away_ladies": [
"MANDY",
"CHLOE",
"LUCIE"
],
"raw_scores": [
{
"game_number": 1,
"home_aces": 21,
"away_aces": 20
},
{
"game_number": 2,
"home_aces": 21,
"away_aces": 17
},
{
"game_number": 3,
"home_aces": 18,
"away_aces": 21
},
{
"game_number": 4,
"home_aces": 15,
"away_aces": 21
},
{
"game_number": 5,
"home_aces": 21,
"away_aces": 15
},
{
"game_number": 6,
"home_aces": 21,
"away_aces": 12
},
{
"game_number": 7,
"home_aces": 19,
"away_aces": 21
},
{
"game_number": 8,
"home_aces": 12,
"away_aces": 21
},
{
"game_number": 9,
"home_aces": 21,
"away_aces": 17
},
{
"game_number": 10,
"home_aces": 10,
"away_aces": 0
},
{
"game_number": 11,
"home_aces": 21,
"away_aces": 10
},
{
"game_number": 12,
"home_aces": 21,
"away_aces": 10
},
{
"game_number": 13,
"home_aces": 20,
"away_aces": 21
},
{
"game_number": 14,
"home_aces": 14,
"away_aces": 21
},
{
"game_number": 15,
"home_aces": 13,
"away_aces": 21
},
{
"game_number": 16,
"home_aces": 21,
"away_aces": 10
},
{
"game_number": 17,
"home_aces": 21,
"away_aces": 18
},
{
"game_number": 18,
"home_aces": 0,
"away_aces": 0
}
]
},
"final_tabular_data": [
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 1,
"home_player_1": "RAZEM",
"home_player_2": "STEVE HUCKST",
"away_player_1": "LIEM",
"away_player_2": "GEORGE",
"home_score": 21,
"away_score": 20
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 2,
"home_player_1": "RAZEM",
"home_player_2": "STEVE HUCKST",
"away_player_1": "LIEM",
"away_player_2": "GEORGE",
"home_score": 21,
"away_score": 17
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 3,
"home_player_1": "JULIE HORSFALL",
"home_player_2": "RACHEL",
"away_player_1": "CHLOE",
"away_player_2": "LUCIE",
"home_score": 18,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 4,
"home_player_1": "JULIE HORSFALL",
"home_player_2": "RACHEL",
"away_player_1": "CHLOE",
"away_player_2": "LUCIE",
"home_score": 15,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 5,
"home_player_1": "TREVOR MORTON",
"home_player_2": "STEVE HUCKST",
"away_player_1": "AARON",
"away_player_2": "GEORGE",
"home_score": 21,
"away_score": 15
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 6,
"home_player_1": "TREVOR MORTON",
"home_player_2": "STEVE HUCKST",
"away_player_1": "AARON",
"away_player_2": "GEORGE",
"home_score": 21,
"away_score": 12
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 7,
"home_player_1": "ALISON ADAMS",
"home_player_2": "RACHEL",
"away_player_1": "MANDY",
"away_player_2": "LUCIE",
"home_score": 19,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 8,
"home_player_1": "ALISON ADAMS",
"home_player_2": "RACHEL",
"away_player_1": "MANDY",
"away_player_2": "LUCIE",
"home_score": 12,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 9,
"home_player_1": "RAZEM",
"home_player_2": "JULIE HORSFALL",
"away_player_1": "LIEM",
"away_player_2": "CHLOE",
"home_score": 21,
"away_score": 17
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 10,
"home_player_1": "RAZEM",
"home_player_2": "JULIE HORSFALL",
"away_player_1": "LIEM",
"away_player_2": "CHLOE",
"home_score": 10,
"away_score": 0
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 11,
"home_player_1": "TREVOR MORTON",
"home_player_2": "ALISON ADAMS",
"away_player_1": "AARON",
"away_player_2": "MANDY",
"home_score": 21,
"away_score": 10
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 12,
"home_player_1": "TREVOR MORTON",
"home_player_2": "ALISON ADAMS",
"away_player_1": "AARON",
"away_player_2": "MANDY",
"home_score": 21,
"away_score": 10
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 13,
"home_player_1": "STEVE HUCKST",
"home_player_2": "RACHEL",
"away_player_1": "GEORGE",
"away_player_2": "LUCIE",
"home_score": 20,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 14,
"home_player_1": "STEVE HUCKST",
"home_player_2": "RACHEL",
"away_player_1": "GEORGE",
"away_player_2": "LUCIE",
"home_score": 14,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 15,
"home_player_1": "RAZEM",
"home_player_2": "JULIE HORSFALL",
"away_player_1": "AARON",
"away_player_2": "MANDY",
"home_score": 13,
"away_score": 21
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 16,
"home_player_1": "RAZEM",
"home_player_2": "JULIE HORSFALL",
"away_player_1": "AARON",
"away_player_2": "MANDY",
"home_score": 21,
"away_score": 10
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 17,
"home_player_1": "TREVOR MORTON",
"home_player_2": "ALISON ADAMS",
"away_player_1": "LIEM",
"away_player_2": "CHLOE",
"home_score": 21,
"away_score": 18
},
{
"match_date": "4/9/26",
"home_team": "OAKAATE",
"away_team": "ST PHILLIP",
"game_number": 18,
"home_player_1": "TREVOR MORTON",
"home_player_2": "ALISON ADAMS",
"away_player_1": "LIEM",
"away_player_2": "CHLOE",
"home_score": 0,
"away_score": 0
}
]
}

And I get this for all 18 games.

That is now useful data rather than an image of a handwritten scorecard.

The complete workflow

So the whole process is:

                 OneDrive
│
▼
Power Automate
│
Convert image
to Base64
│
▼
ngrok
│
▼
FastAPI
│
▼
LangGraph
│
▼
Qwen Vision
│
▼
Extract JSON
│
▼
Validator
│
┌─────────┴─────────┐
│ │
Valid Invalid
│ │
▼ ▼
Compiler Retry Qwen
│ │
│ max 3 attempts
│ │
▼ ▼
Clean JSON Failure

What I would improve for production

This is currently a practical local solution, but there are several things I would improve before considering it a production service.

Secure the API

Because ngrok exposes the API through an HTTPS address, I would add authentication or a secret header so that anybody who discovers the URL cannot submit images.

Validate the games

Checking for 18 games is only the first validation rule.

I could also check:

  • game numbers are 1–18
  • no duplicate game numbers
  • no missing scores
  • scores are numeric
  • player names aren’t empty
  • all required teams are present

Prevent duplicate processing

If the same image is uploaded twice, I don’t want two copies of the match in my database.

A file ID or hash could be stored with the processed record.

Manual review

If all three AI attempts fail, I could move the image into a manual-review folder or send myself a notification.

That gives me a useful fallback instead of silently losing the match.

The important lesson

The biggest lesson from this project isn’t actually the Qwen model.

It’s the workflow around it.

AI is very good at looking at something messy like a handwritten scorecard and turning it into structured information.

But AI can make mistakes.

So rather than assuming the AI is always correct, I built a small system around it:

AI extraction
↓
Validation
↓
Feedback
↓
Retry
↓
Deterministic processing
↓
Clean data

For my badminton project, this means that a weekly handwritten scorecard can now become structured data automatically.

And because I’m running Qwen through Ollama locally, the core AI processing is happening on my own machine.

What started as:

“I have a pile of handwritten badminton scorecards.”

has become:

“I have a repeatable data pipeline that turns those scorecards into structured match data.”

And that is where I think agentic AI becomes particularly useful — not just asking an AI model a question, but building a workflow around the model that can check the result, react to errors, and produce something an application can actually use.

Posted in Uncategorized | Tagged , , , , , , , , , , , , , , | Leave a comment

Calling the Dataverse Web API from Python Using Certificate Authentication

Introduction

In my previous post, I covered how to configure certificate authentication for Power Automate and Dataverse, including creating the certificate, registering it with Microsoft Entra ID, creating the Dataverse Application User, and configuring the certificate-based connection.

Certificate Authentication for Power Automate & Dataverse: Why It Matters and How to Implement It

This post assumes that setup has already been completed.

Instead, let’s look at the next step:

How can we use the same certificate from Python to authenticate and call the Dataverse Web API?

The basic flow

The Python application uses the private key from the .pfx certificate to authenticate the application with Microsoft Entra ID.

The flow is:

Python
│
│ Load .pfx
▼
Private Key + Certificate
│
│ MSAL certificate authentication
▼
Microsoft Entra ID
│
│ Access Token
▼
Dataverse Web API
│
▼
Accounts / Contacts / Custom Tables

The important point is that Python does not send the PFX file to Dataverse.

The certificate is used to authenticate the application with Microsoft Entra ID and obtain an access token. That access token is then sent to Dataverse in the HTTP Authorization header.

Python packages

For this example, I’m using:

  • msal — Microsoft Authentication Library
  • requests — to call the Dataverse Web API
  • cryptography — to read the private key from the PFX file

Install them with:

pip install msal requests cryptography

Configuration

For this demonstration, we need:

TENANT_ID = "your-tenant-id"
CLIENT_ID = "your-application-client-id"
DATAVERSE_URL = "https://yourorg.crm.dynamics.com"
PFX_PATH = r"C:\certs\spinhope.pfx"
PFX_PASSWORD = b"YourStrongPassword"
CERT_THUMBPRINT = "398CF6DB1E8EEAE44ED23AF4723087B43C43EDB9"

For a real application, I would recommend storing these values in environment variables or another secure configuration mechanism rather than putting passwords and other configuration values directly in the source code.

Reading the PFX certificate

The PFX contains the private key and certificate.

Python’s cryptography library can load these from the PFX:

from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.primitives.serialization import pkcs12
with open(PFX_PATH, "rb") as f:
pfx_data = f.read()
private_key_obj, certificate, additional_certificates = (
pkcs12.load_key_and_certificates(
pfx_data,
PFX_PASSWORD
)
)
if private_key_obj is None:
raise ValueError("The PFX does not contain a private key.")

We then convert the private key to PEM format because that is the format we will provide to MSAL:

private_key_pem = private_key_obj.private_bytes(
encoding=serialization.Encoding.PEM,
format=serialization.PrivateFormat.PKCS8,
encryption_algorithm=serialization.NoEncryption(),
).decode("utf-8")

Creating the MSAL client

Now we can create an MSAL confidential client.

import msal
app = msal.ConfidentialClientApplication(
CLIENT_ID,
authority=f"https://login.microsoftonline.com/{TENANT_ID}",
client_credential={
"private_key": private_key_pem,
"thumbprint": CERT_THUMBPRINT.replace(":", "").upper(),
},
)

There are two important certificate-related values here:

"private_key": private_key_pem

and:

"thumbprint": CERT_THUMBPRINT.replace(":", "").upper()

The private key is used to create the cryptographic proof required for certificate-based application authentication.

The thumbprint identifies the certificate that has been registered against the application in Microsoft Entra ID.

Why do we need the certificate thumbprint?

The thumbprint can be confusing when you first look at certificate authentication in Python.

A certificate has a unique fingerprint, commonly called its thumbprint. It is a hash that identifies that particular certificate.

When you register a certificate with the Microsoft Entra application, Microsoft Entra stores the public certificate. Your Python application has the corresponding private key.

During certificate-based authentication, the application proves that it possesses the private key. The thumbprint identifies the certificate associated with that private key.

In our Python example, we therefore provide both:

{
"private_key": private_key_pem,
"thumbprint": CERT_THUMBPRINT
}

The private key is used to create the cryptographic proof, while the thumbprint identifies the certificate associated with that key.

This is why the thumbprint is present in the MSAL configuration.

Is the thumbprint Python-specific?

No.

The concept of the certificate thumbprint is not specific to Python.

What differs between languages is how the authentication library expects the certificate to be supplied.

For example, in our Python example, MSAL receives:

client_credential={
"private_key": private_key_pem,
"thumbprint": CERT_THUMBPRINT,
}

The thumbprint is therefore explicitly supplied.

Why don’t I need to specify the thumbprint in C#?

This is where the difference between certificate authentication and the MSAL library implementation becomes important.

In C#, you can load the certificate as an X509Certificate2 object and pass that certificate directly to MSAL.NET:

var certificate = new X509Certificate2(
"certificate.pfx",
"password"
);
var app = ConfidentialClientApplicationBuilder
.Create(clientId)
.WithCertificate(certificate)
.WithAuthority(authority)
.Build();

You don’t explicitly write something such as:

.WithThumbprint(...)

because the X509Certificate2 object already contains the certificate information that MSAL.NET needs.

The certificate still has a thumbprint. You simply don’t have to provide it as a separate parameter in this C# API.

So it is better to think of this as a difference in the API exposed by the authentication libraries, rather than a difference in the underlying authentication mechanism.

The underlying concept is still:

Certificate
│
├── Public certificate
│
├── Private key
│
└── Thumbprint

The different MSAL libraries simply expose these pieces differently.

What about other languages?

The same principle applies to other MSAL libraries.

Some APIs accept a certificate object, while others allow or require the certificate information to be supplied separately.

Therefore, don’t think of it as:

Python → needs thumbprint
C# → doesn't need thumbprint

A better way to think about it is:

              Certificate Authentication
                       │
              ┌────────┴────────┐
              │                 │
         Private Key      Public Certificate
              │                 │
              │              Thumbprint
              │                 │
              └────────┬────────┘
                       │
                 MSAL Library
                       │
                       ▼
                Microsoft Entra ID

The certificate itself has a thumbprint regardless of the programming language.

It is simply a question of whether the particular authentication library requires you to provide that thumbprint explicitly.

An important point about MSAL Python

There is another detail worth mentioning.

Current versions of MSAL Python also support certificate configuration where the library can work from a PFX file directly. In that scenario, MSAL can obtain the certificate information itself.

For this article, however, I’m deliberately using the explicit:

private_key + thumbprint

approach because it makes it easier to understand what is happening during certificate-based authentication.

You can obtain the Thumbprint from App registration > Certificates & secrets

Requesting an access token

We can now request an application access token using the client credentials flow:

auth_result = app.acquire_token_for_client(
scopes=[f"{DATAVERSE_URL}/.default"]
)

If authentication succeeds, MSAL returns an access token:

if "access_token" in auth_result:
print("Authentication successful!")

The .default scope tells Microsoft Entra ID to issue a token based on the application’s configured permissions for the Dataverse resource.

At this point, the certificate has done its job.

We have authenticated the application and obtained an access token.

Calling the Dataverse Web API

Once we have the token, calling Dataverse is simply an HTTP request.

headers = {
"Authorization": f"Bearer {auth_result['access_token']}",
"OData-MaxVersion": "4.0",
"OData-Version": "4.0",
"Accept": "application/json",
"Content-Type": "application/json",
}
api_endpoint = (
f"{DATAVERSE_URL}/api/data/v9.2/accounts?$top=3"
)
response = requests.get(
api_endpoint,
headers=headers,
timeout=30
)

If the request succeeds:

if response.ok:
print(response.json())
else:
print(f"Dataverse API Error ({response.status_code}):")
print(response.text)

You should now see the first three account records returned by Dataverse.

Complete example

Putting everything together:

import msal
import requests
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.primitives.serialization import pkcs12
TENANT_ID = "your-tenant-id"
CLIENT_ID = "your-client-id"
DATAVERSE_URL = "https://yourorg.crm.dynamics.com"
PFX_PATH = r"C:\certs\spinhope.pfx"
PFX_PASSWORD = b"YourStrongPassword"
CERT_THUMBPRINT = "398CF6DB1E8EEAE44ED23AF4723087B43C43EDB9"
# Load the PFX
with open(PFX_PATH, "rb") as f:
pfx_data = f.read()
private_key_obj, certificate, additional_certificates = (
pkcs12.load_key_and_certificates(
pfx_data,
PFX_PASSWORD
)
)
if private_key_obj is None:
raise ValueError("The PFX does not contain a private key.")
# Convert the private key to PEM
private_key_pem = private_key_obj.private_bytes(
encoding=serialization.Encoding.PEM,
format=serialization.PrivateFormat.PKCS8,
encryption_algorithm=serialization.NoEncryption(),
).decode("utf-8")
# Create MSAL confidential client
app = msal.ConfidentialClientApplication(
CLIENT_ID,
authority=f"https://login.microsoftonline.com/{TENANT_ID}",
client_credential={
"private_key": private_key_pem,
"thumbprint": CERT_THUMBPRINT.replace(":", "").upper(),
},
)
# Request an access token
print("Requesting token from Microsoft Entra ID...")
auth_result = app.acquire_token_for_client(
scopes=[f"{DATAVERSE_URL}/.default"]
)
# Call Dataverse
if "access_token" in auth_result:
print("Authentication successful! Querying Dataverse...")
headers = {
"Authorization": f"Bearer {auth_result['access_token']}",
"OData-MaxVersion": "4.0",
"OData-Version": "4.0",
"Accept": "application/json",
"Content-Type": "application/json",
}
api_endpoint = (
f"{DATAVERSE_URL}/api/data/v9.2/accounts?$top=3"
)
response = requests.get(
api_endpoint,
headers=headers,
timeout=30
)
if response.ok:
print(response.json())
else:
print(f"Dataverse API Error ({response.status_code}):")
print(response.text)
else:
print("Authentication failed:")
print(
auth_result.get(
"error_description",
auth_result.get("error")
)
)

Python vs Power Automate

What’s interesting about this approach is that the same application identity and certificate can be used by different technologies.

For example:

                    Microsoft Entra ID
                           │
                    Application + Cert
                           │
              ┌────────────┴────────────┐
              │                         │
       Power Automate                Python
              │                         │
              ▼                         ▼
       Dataverse                    Dataverse

Power Automate can use the certificate through its Dataverse connection, while Python can use the same application identity through MSAL.

The important part is that both are authenticating as the application, rather than depending on an individual’s username and password.

One important security point

The .pfx contains the private key.

That means it should be treated as a secret.

Don’t commit it to GitHub, don’t put it inside your source repository, and don’t include the PFX password in your Python source code for production applications.

For a demonstration, a local PFX file is convenient. For production workloads, the private key should be stored and protected using an appropriate secret or certificate-management solution.

Conclusion

Once the certificate and Dataverse Application User are configured, calling Dataverse from Python is relatively straightforward.

The important pieces are:

  1. Load the PFX.
  2. Extract the private key.
  3. Give the private key and certificate thumbprint to MSAL.
  4. Request an application access token.
  5. Send that token to the Dataverse Web API.

The certificate setup itself is covered in my previous article. This post simply shows how the same certificate-based application identity can be used from Python to call Dataverse directly.

This approach is particularly useful for background jobs, integrations, scripts, data-processing applications and other non-interactive workloads where there is no person sitting in front of the application to sign in.

Posted in Uncategorized | Tagged , , , , , , , , , , , , | Leave a comment

Calling the Dataverse Web API from C# with a Certificate

Certificate authentication is useful when a background process needs to work with Dataverse without requiring a person to sign in.

A console application, scheduled job, integration service, or worker can authenticate as a Microsoft Entra application, obtain an access token, and call the Dataverse Web API securely.

This article shows how to use C# and Microsoft Authentication Library (MSAL) with a certificate to authenticate against Microsoft Entra ID and then call the Dataverse Web API.

This is the C# companion article to: Certificate Authentication for Power Automate & Dataverse: Why It Matters and How to Implement It

That article explains how to:

  • Create the certificate.
  • Export the .pfx and .cer files.
  • Upload the public certificate to the Microsoft Entra app registration.
  • Create the Dataverse Application User.

Complete those steps before running the C# code below.


What the Application Does

The application uses the OAuth 2.0 client-credentials flow, with a certificate instead of a client secret.

The process is:

  1. The application loads the private certificate from a .pfx file.
  2. MSAL uses the private key to create a client assertion.
  3. Microsoft Entra ID verifies the certificate against the public certificate registered on the app registration.
  4. Entra ID returns an access token.
  5. The application sends the access token to the Dataverse Web API.
  6. Dataverse authorizes the request using the application’s Application User and assigned security role.

The important distinction is that the certificate is used for authentication, while the Dataverse Application User and security roles control authorization.

PFX vs CER

The two certificate files have different purposes:

  • .pfx — contains the private key and must be kept secure.
  • .cer — contains the public certificate and is uploaded to the Microsoft Entra app registration.

The .pfx is the sensitive file. Never commit it or its password to source control.


Prerequisites

Before running the sample, you need:

  • A Microsoft Entra app registration.
  • The public .cer certificate uploaded under Certificates & secrets.
  • A Dataverse Application User associated with the app registration.
  • A suitable Dataverse security role assigned to the Application User.
  • The matching private .pfx certificate.
  • The password for the .pfx file.
  • The .NET SDK. (https://dotnet.microsoft.com/en-us/download/dotnet/10.0)
  • The Microsoft.Identity.Client NuGet package.

Create the C# Console Application

Create a new console application:

dotnet new console --name DataverseCertificateSample
cd DataverseCertificateSample

Install MSAL:

dotnet add package Microsoft.Identity.Client

The C# Sample

The following example targets .NET 10 and retrieves three Account records from Dataverse.

Replace the placeholder values with your own tenant, application and Dataverse details.

The PFX password is deliberately read from an environment variable rather than being stored in the source code.

using Microsoft.Identity.Client;
using System.Net.Http.Headers;
using System.Security.Cryptography.X509Certificates;
var tenantId = "YOUR_TENANT_ID";
var clientId = "YOUR_APPLICATION_CLIENT_ID";
var dataverseUrl = "https://YOUR-ORGANIZATION.crm.dynamics.com";
var pfxPath = @"C:\certs\dataverse-app.pfx";
// Here you can use environment variable to get the password or hardcode it.
var pfxPassword = Environment.GetEnvironmentVariable("DATAVERSE_PFX_PASSWORD")
?? throw new InvalidOperationException(
"Set the DATAVERSE_PFX_PASSWORD environment variable before running the application.");
// OR var pfxPassword = "PasteYourStrongPasswordHere";
// X509CertificateLoader is the current API for loading PFX files in .NET 9+.
using var certificate = X509CertificateLoader.LoadPkcs12FromFile(
pfxPath,
pfxPassword,
X509KeyStorageFlags.EphemeralKeySet);
var app = ConfidentialClientApplicationBuilder
.Create(clientId)
.WithAuthority($"https://login.microsoftonline.com/{tenantId}")
.WithCertificate(certificate)
.Build();
var tokenResult = await app
.AcquireTokenForClient(new[] { $"{dataverseUrl}/.default" })
.ExecuteAsync();
using var httpClient = new HttpClient();
httpClient.DefaultRequestHeaders.Authorization =
new AuthenticationHeaderValue("Bearer", tokenResult.AccessToken);
httpClient.DefaultRequestHeaders.Add("OData-MaxVersion", "4.0");
httpClient.DefaultRequestHeaders.Add("OData-Version", "4.0");
httpClient.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/json"));
var endpoint =
$"{dataverseUrl}/api/data/v9.2/accounts" +
"?$select=name,accountnumber,telephone1&$top=3";
var response = await httpClient.GetAsync(endpoint);
var responseBody = await response.Content.ReadAsStringAsync();
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Data retrieved successfully:");
Console.WriteLine(responseBody);
}
else
{
Console.WriteLine($"Dataverse API Error ({(int)response.StatusCode}):");
Console.WriteLine(responseBody);
}

Set the PFX Password

Before running the application, set the PFX password as an environment variable.

In PowerShell:

$env:DATAVERSE_PFX_PASSWORD = "your-PFX-password"
dotnet run

This keeps the password out of the C# source code.

For production workloads, you should go further and retrieve the certificate and/or secret from a managed secret store rather than relying on a local PFX file.


What Happens During Authentication?

There are several steps involved when the application runs.

1. The PFX is loaded

The application loads the private certificate from the PFX file.

The private key is important because it allows the application to prove ownership of the certificate.

2. MSAL creates the client assertion

MSAL uses the certificate to authenticate the application to Microsoft Entra ID.

The application is identified using its Client ID, while the certificate provides the cryptographic proof that the application possesses the private key associated with the registered public certificate.

3. Microsoft Entra ID issues an access token

If the certificate matches the certificate registered against the application and the application is otherwise correctly configured, Microsoft Entra ID issues an OAuth access token.

4. The token is sent to Dataverse

The application places the token in the HTTP Authorization header:

Authorization: Bearer <access-token>

5. Dataverse performs authorization

Dataverse then determines what the application is allowed to do based on the Application User and its assigned security roles.

The overall flow is:

C# Application
|
| PFX + Private Key
v
Microsoft Entra ID
|
| Access Token
v
Dataverse Web API
|
| Application User
v
Dataverse Security Role
|
v
Dataverse Data

Why Use EphemeralKeySet?

The sample loads the certificate using:

X509KeyStorageFlags.EphemeralKeySet

This keeps the private key in memory instead of persisting it to the local certificate store.

That can be useful for applications such as:

  • Console applications
  • Background workers
  • Scheduled jobs
  • Integration services
  • Containerized applications

If your hosting environment has a specific certificate-store requirement, choose the certificate storage option appropriate for that environment.

The important security principle is that the private key should be protected throughout its lifetime.


Is a Certificate Thumbprint Required?

You may notice that the C# example does not contain a certificate thumbprint.

This does not mean that the certificate does not have a thumbprint.

Every certificate has identifying information such as its thumbprint. The important distinction is whether your application code needs to explicitly provide that thumbprint to the authentication library.

That is language- and library-dependent. I will explain in another blog about a python sample code where it consumes the certificate thumbprint.

C# with MSAL

In this example, the certificate is loaded into an X509Certificate2 object:

using var certificate = X509CertificateLoader.LoadPkcs12FromFile(
pfxPath,
pfxPassword,
X509KeyStorageFlags.EphemeralKeySet);

It is then passed directly to MSAL:

.WithCertificate(certificate)

MSAL can obtain the certificate information it needs from the loaded certificate, including its thumbprint.

Therefore, there is no need to hard-code the thumbprint separately in this C# example.

What About Python?

This can be different in Python.

Some Python authentication implementations and libraries use the certificate’s thumbprint as part of the certificate credential configuration.

Depending on the library and authentication approach, you may need to provide information such as:

  • Tenant ID
  • Client ID
  • Certificate thumbprint
  • Certificate/private-key information

Therefore, you should not assume that the C# implementation can be copied directly to Python.

The correct approach is to check the requirements of the particular Python authentication library you are using.

The key point is:

Whether a thumbprint needs to be explicitly supplied by your application is language- and library-dependent.

The certificate itself still has a thumbprint regardless of the programming language.


The Thumbprint Is Still Important

Even though the C# code does not explicitly specify a thumbprint, Microsoft Entra ID still needs to have the matching public certificate registered against the app registration.

This is particularly important when troubleshooting certificate authentication.

For example, if you receive:

AADSTS700027

check the certificate configuration.

Common causes include:

  • The public certificate has not been uploaded to the app registration.
  • The certificate has expired.
  • The .cer does not correspond to the .pfx.
  • The application is loading a different PFX than expected.
  • The certificate registered in Entra ID has been removed.

Compare the certificate thumbprint shown in Microsoft Entra ID with the thumbprint of the certificate contained in the PFX.

If they do not match, upload the correct public .cer certificate.

So, even when the thumbprint is not explicitly present in your C# code, it remains a very useful value when validating and troubleshooting the certificate configuration.


Authentication and Authorization Are Separate

One of the most important concepts when troubleshooting Dataverse application authentication is that authentication and authorization are separate steps.

A successful token request proves that Microsoft Entra ID trusts the application.

It does not automatically give the application access to Dataverse data.

For example, you might successfully receive an access token but then receive a Dataverse error such as:

The user is not a member of the organization

or an HTTP:

403 Forbidden

In that situation, the certificate authentication may actually be working correctly.

The problem may instead be the Dataverse Application User configuration.

Check that:

  1. The Application User exists in the target Dataverse environment.
  2. The Application User is associated with the correct Microsoft Entra application.
  3. The Application User has an appropriate security role.
  4. The security role grants access to the tables and operations required by the application.

Think of it as two separate questions:

Microsoft Entra ID asks:

“Can I trust that this application really is who it says it is?”

Dataverse asks:

“Now that I know who this application is, what is it allowed to do?”


Testing with System Administrator

When initially troubleshooting the configuration, assigning the Application User the System Administrator security role can be useful as a temporary connectivity test.

If the C# application works with System Administrator permissions, you have established that:

  • Certificate authentication works.
  • Microsoft Entra authentication works.
  • The Application User is correctly configured.
  • The application can access the Dataverse environment.

However, do not use System Administrator as the permanent role for a production integration simply because it makes the problem disappear.

Once connectivity has been confirmed, replace it with a least-privilege security role containing only the permissions the application actually requires.

For this sample, the Application User needs appropriate Read permission on the Account table.


Understanding the Dataverse Web API URL

The sample calls:

https://YOUR-ORGANIZATION.crm.dynamics.com/api/data/v9.2/accounts

The important part is:

/api/data/v9.2/

This is the Dataverse Web API endpoint.

The accounts segment identifies the Account table.

The query:

?$select=name,accountnumber,telephone1&$top=3

asks Dataverse to return only:

  • name
  • accountnumber
  • telephone1

and limits the result to three records.

Using $select is preferable to retrieving every column because it reduces the amount of data returned by the API and makes the request more explicit.


Why Use the Web API from C#?

Dataverse provides several ways to interact with data from code.

The Web API is particularly useful when you want to work with Dataverse using standard HTTP and OData concepts.

For example, the same authentication pattern can be used by applications that need to:

  • Read Dataverse records.
  • Create records.
  • Update records.
  • Delete records.
  • Execute Dataverse actions.
  • Call custom APIs.
  • Integrate Dataverse with external systems.

Because the authentication is based on an application identity rather than an interactive user, it is well suited to non-interactive workloads.


Production Considerations

The sample deliberately keeps the implementation simple so that the authentication flow is easy to understand.

A production application should also consider:

  • Certificate storage.
  • Certificate rotation.
  • Secret management.
  • Logging.
  • Retry handling.
  • HTTP client lifetime.
  • Least-privilege Dataverse permissions.
  • Monitoring.
  • Failure handling.

For example, instead of storing the PFX on a local machine, a production workload could retrieve the certificate from a managed service such as Azure Key Vault.

Likewise, certificate rotation should be planned rather than waiting for the certificate to expire.


Certificate Rotation

Certificates eventually expire, so certificate rotation needs to be part of the application’s lifecycle.

A typical rotation process is:

  1. Create a new certificate.
  2. Export the new public .cer.
  3. Upload the new public certificate to the Entra app registration.
  4. Make the new private certificate available to the application.
  5. Deploy the application using the new certificate.
  6. Test authentication.
  7. Remove the old certificate after all applications have moved to the new certificate.

Keeping both certificates registered temporarily provides a controlled transition rather than creating a last-minute outage when the old certificate expires.


Production Checklist

Before deploying a certificate-authenticated Dataverse integration to production:

  • Never commit the PFX file to Git.
  • Never commit the PFX password to source control.
  • Store certificates and secrets in a managed secret store such as Azure Key Vault where possible.
  • Give the Dataverse Application User only the permissions it actually needs.
  • Monitor the certificate expiration date.
  • Upload replacement certificates before the existing certificate expires.
  • Test certificate rotation before it becomes an emergency.
  • Keep the old certificate available during a controlled rotation window.
  • Remove the old certificate once all applications have successfully moved to the new certificate.
  • Avoid using System Administrator as the permanent role for an integration.
  • Log authentication and API failures without exposing access tokens, private keys or certificate passwords.

Final Thoughts

Certificate authentication provides a useful way of running non-interactive C# integrations with Dataverse.

There is no interactive user login and no client secret embedded in the application. Instead, the application proves its identity using a private certificate, Microsoft Entra ID issues an access token, and Dataverse uses the Application User and security roles to determine what the application can access.

The same certificate-based authentication pattern can support different workloads, including:

  • C# console applications
  • Background workers
  • Scheduled jobs
  • Integration services
  • Power Automate
  • Other applications and automation platforms

One detail that is easy to overlook is the certificate thumbprint.

The thumbprint is part of the certificate’s identity, but whether you need to explicitly specify it in your application is dependent on the programming language and authentication library. C# with MSAL can work directly with the loaded certificate, whereas some Python authentication approaches may require the thumbprint to be supplied explicitly.

The key concepts to remember are:

The certificate authenticates the application.

The Dataverse Application User and security role authorize what that application can do.

Once that distinction is clear, troubleshooting certificate-based Dataverse authentication becomes much easier.

Posted in Uncategorized | Tagged , , , , , , , , , | Leave a comment

Streamlining Power Platform Deployments: A Guide to PAC CLI and Solution Settings Files

Deploying solutions across Power Platform environments (from Development to UAT and Production) used to mean a lot of manual clicking, re-authenticating, and post-deployment configuration.

If you want to move toward Application Lifecycle Management (ALM) and automate your deployment pipelines, the Microsoft Power Platform CLI (pac) is your best friend.

In this guide, we will walk through how to seamlessly manage your environment connections, generate deployment templates, and handle connection references using the CLI.


1. Managing Your Environments like a Pro

Before moving solutions, you need to tell the CLI where to look. Instead of logging in and out constantly, pac lets you maintain multiple authenticated profiles.

Connect to Your Target Environment

To connect directly to a specific environment (like your Development or UAT instance), use the auth create command. Pass your absolute environment URL or unique GUID into the flag:

bash

pac auth create --environment "00000000-0000-0000-0000-000000000000"

Use code with caution.

💡 Tip for headless environments: If you are running this inside GitHub Codespaces, Docker, or WSL2 where an interactive browser window cannot pop up, simply append --deviceCode to authenticate via a device login page.

List and Switch Profiles

To see every environment you have access to on your machine, run:

bash

pac auth list

Use code with caution.

This returns a clean index table. The profile marked with an asterisk (*) is your current active target. To switch your context immediately to another profile (e.g., switching from Dev to UAT), select it by its index number:

bash

pac auth select --index 1

Use code with caution.


2. Preparing for the Move: Managed vs. Unmanaged Export

When exporting a solution, you need to decide whether it should be deployed as Managed or Unmanaged in your target environment.

  • Managed Solution (Recommended for UAT/Prod): Locks down components so they cannot be accidentally edited in the target environment. This is standard practice for clean environment lifecycles.
  • Unmanaged Solution (Recommended for Dev-to-Dev): Leaves components fully open to modification. Use this if you are moving changes between sandbox developer environments where customization is still required.

Here is how to run the pac solution export command for either scenario:

Option A: Export as a Managed Solution

bash

pac solution export --path SalesProject_1_0_0_1_managed.zip --name SalesProject --managed

Use code with caution.

Option B: Export as an Unmanaged Solution

To export it as unmanaged, simply omit the --managed flag from your command:

bash

pac solution export --path SalesProject_1_0_0_1.zip --name SalesProject

Use code with caution.


3. The Secret Sauce: Generating a Settings File

When your solution lands in UAT, it needs to link up to UAT’s local data connections and environment variables. Hardcoding these in development is a massive anti-pattern.

Instead, we can use pac to dissect our exported zip file (whether managed or unmanaged) and generate a clean configuration template:

bash

pac solution create-settings --solution-zip SalesProject_1_0_0_1_managed.zip --settings-file settings.json

Use code with caution.

The CLI does the heavy lifting, scanning your solution for connection schemas and mapping out a structure that looks like this:

json

{
"EnvironmentVariables": [],
"ConnectionReferences": [
{
"LogicalName": "ajb_crmdevconnection",
"ConnectionId": "",
"ConnectorId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps"
}
],
"CopilotAgents": []
}

Use code with caution.

Notice that the "ConnectionId" field is empty. This is exactly what we need to populate before importing.


4. 🧠 The “Key & Value” Concept: Hands Off the LogicalName!

When you look at the generated file, you will notice a field called "LogicalName": "ajb_crmdevconnection". A very common point of confusion is wondering if you need to manually change this name or create it inside your UAT environment before deploying.

The short answer is no.

Think of this JSON setup exactly like a dictionary or a Map in programming:

  • The Key (LogicalName): ajb_crmdevconnection acts as a permanent, unique anchor. The flows and apps inside your solution are hardcoded to look for this exact key. Never edit this field.
  • The Value (ConnectionId): This is the physical connection string. This is the only thing you change.

You do not need to build the LogicalName component in UAT. During the import, the platform automatically reads this key, extracts the connection reference component from your zip file, builds it inside UAT for you, and injects the UAT-specific ConnectionId you provided.


5. The Target Pre-Requisite: Gathering Connection IDs

While the platform creates the component, it cannot guess who should own it or what credentials to use. You must manually establish the connection in the target environment first.

How to get your Connection ID:

  1. Open the Power Apps Portal and switch to your target UAT environment.
  2. Navigate to Connections in the left menu (you may need to click More -> Discover all).
  3. Click New Connection, search for Microsoft Dataverse, and authenticate it (ideally using a dedicated UAT service account).
  4. Click on your newly created connection. Look up at your browser’s URL address bar.
  5. Copy the long alphanumeric string found right after the shared_commondataserviceforapps:
    ...connections/shared_commondataserviceforapps/0fdf44de9c0c4844994e2e2e62490a38/details

Paste that string directly into the "ConnectionId" (0fdf44de9c0c4844994e2e2e62490a38) value placeholder in your settings.json file.


6. Final Step: Import and Deploy

With your updated settings.json file tailored specifically for UAT, switch your CLI context to UAT (pac auth select --index <UAT_index>) and run the import.

Pass both the solution package and the settings file:

bash

pac solution import --path SalesProject_1_0_0_1_managed.zip --settings-file settings.json

Use code with caution.

The platform will unpack your solution, create your components, and seamlessly bind the internal LogicalName to the pre-existing UAT Dataverse connection.

Moving to DevOps and CI/CD

While executing these steps locally from your terminal is great for testing your logic, this setup is designed for automation. If you transition to tools like Azure DevOps or GitHub Actions, you can store these environment-specific Connection IDs as pipeline secrets, inject them into the settings.json dynamically via code, and achieve fully automated, zero-touch deployments!


Posted in Uncategorized | Tagged , , , , , , , , , , , , , | Leave a comment

Certificate Authentication for Power Automate & Dataverse: Why It Matters and How to Implement It

Modern enterprise automation needs to be secure, reliable, and independent of human accounts. If your Power Automate flows integrate with Dataverse, using certificate‑based authentication is the strongest and most future‑proof method available.

This guide explains:

  • What certificate authentication is
  • Why it’s better than OAuth
  • How to configure it end‑to‑end
  • How to maintain it long‑term

See below Power Automate Microsoft Dataverse. Click new connection reference. Follow the below steps how to configure this.

Let’s dive in.

🧩 What Is Certificate Authentication?

Certificate authentication is a non‑interactive, cryptographic method where an application proves its identity using:

  • A public key (stored in Azure AD)
  • A private key (stored in a .pfx file used by Power Automate)

Instead of passwords or user logins, the application presents a signed token using the private key. Azure AD verifies it using the public key.

This is known as the client_credentials OAuth grant type — designed for server‑to‑server automation.

🏆 Why Certificate Authentication Is Better Than OAuth

OAuth (authorization code flow) is designed for human sign‑in. Certificate authentication is designed for applications.

Here’s why certificates win for Dataverse automation:

⭐ 1. No passwords

OAuth depends on user passwords. Certificates do not — they use cryptographic keys.

⭐ 2. No MFA

OAuth requires MFA for most enterprise users. Certificates bypass MFA because they authenticate as an app.

⭐ 3. No user dependency

If the user leaves the company, OAuth flows break. Certificate flows continue running under the Application User.

⭐ 4. No session expiry

OAuth tokens expire and require refresh. Certificate tokens are issued on demand using the private key.

⭐ 5. Stronger security

Certificates cannot be guessed, phished, or brute‑forced. Private keys never leave your environment.

⭐ 6. Clean audit logs

Dataverse shows actions performed by:

Application User (YourAppName)

instead of a personal user account.

⭐ 7. Perfect for enterprise automation

Background jobs, integrations, and managed solutions all benefit from a stable, non‑interactive identity.

🔐 How to Configure Certificate Authentication (End‑to‑End)

Below is the complete setup process — from generating the certificate to binding it inside your solution.

🧩 1. Create a Self‑Signed Certificate

Best practice:

Follow a standard pattern such as:

Code

CN=<domain or service>, O=<company>, OU=<department>, C=<country>

Example for automation:

Code

CN=spinhope-automation, O=Spinhope Ltd, OU=IT, C=GB

Why it matters:

  • Makes certificate inventory predictable
  • Helps avoid confusion during renewals
  • Supports clean governance and auditing

Open PowerShell (Run as Administrator):

See below PowerShell command uses -CertStoreLocation Path

🏢 Your Options for the -CertStoreLocation Path

You can change this path based on who needs to use the certificate on that machine:

  • Current User (Default): Cert:\CurrentUser\My
    • Best for: Testing, development, or user-specific tasks (like signing scripts you run yourself).
    • Security: Only your logged-in Windows account can access the private key. It does not require Admin privileges to run.
  • Local Machine: Cert:\LocalMachine\My
    • Best for: IIS Web Servers, system services, or apps that run in the background (like an API daemon).
    • Security: Available to any authorized application or administrator on that PC. Requires running PowerShell as an Administrator to create.

💡 Pro-Tip: You can omit the parameter entirely

If you completely remove the -CertStoreLocation parameter from your script, PowerShell will automatically default to Cert:\LocalMachine\My. Because of this default, you must run PowerShell as an Administrator, or the command will fail.

powershell

$cert = New-SelfSignedCertificate `
-Subject "CN=spinhope.com, O=Spinhope Ltd, OU=IT, C=GB" `
-CertStoreLocation "Cert:\CurrentUser\My" `
-KeySpec Signature

Export the private key (.pfx)

powershell

Export-PfxCertificate `
-Cert $cert `
-FilePath "C:\certs\spinhope.pfx" `
-Password (ConvertTo-SecureString -String "YourStrongPassword" -Force -AsPlainText)

Export the public key (.cer)

powershell

Export-Certificate -Cert $cert -FilePath "C:\certs\spinhope.cer"

You now have:

  • spinhope.pfx → used by Power Automate
  • spinhope.cer → uploaded to Azure AD

☁️ 2. Upload the Certificate to Azure AD

  1. Go to Azure Portal → Entra ID → App registrations
  2. Open your app. If you don’t have one create an app registration
  3. Go to Certificates & secrets
  4. Upload the .cer file
  5. Confirm the certificate appears with a thumbprint and expiry date

Azure AD now trusts your application’s public key.

🔑 3. Add Dataverse API Permission

  1. Go to API permissions
  2. Click Add a permission
  3. Select Dynamics CRM
  4. Choose user_impersonation
  5. Click Grant admin consent

This is the only permission required for Dataverse.

⚙️ 4. Authentication Settings

Under Authentication (Preview):

  • Ensure Allow public client flows is Disabled
  • No redirect URIs are needed
  • No secrets are needed

This ensures Azure AD uses client_credentials, not OAuth code flow.

🧑‍💼 5. Create an Application User in Dataverse

Dataverse needs to map your Azure AD app to a Dataverse identity.

  1. Open Power Platform Admin Center
  2. Select your environment
  3. Go to Settings → Users + permissions → Application users
  4. Click + New application user
  5. Select your Azure AD app (Step 2 above)
  6. Choose the Business Unit
  7. Assign a Security Role (e.g., System Administrator)
  8. Save

This creates a Dataverse identity for your app.

🔐 6. Create the Certificate Connection in Power Automate

When creating the Dataverse connection:

FieldValue
Authentication TypeClient Certificate
Client IDAzure AD Application (client) ID
Tenant IDAzure AD Directory (tenant) ID
Environment URLe.g., https://org.crm.dynamics.com
Certificate FileUpload .pfx
PasswordThe .pfx password

This creates a secure, non‑interactive connection using the certificate.

🧩 7. Add the Connection to Your Solution

Connections created globally do not automatically appear inside solutions.

To link your flow to the certificate connection:

  1. Open your solution
  2. Click New → Connection Reference
  3. Select Microsoft Dataverse
  4. Choose your certificate‑based connection
  5. Save

Your flows can now use this connection reference.

🔁 Certificate Renewal (When It Expires)

When the certificate expires:

  1. Generate a new certificate
  2. Upload the new .cer to Azure AD
  3. Edit the existing Power Automate connection
  4. Replace the .pfx + password
  5. Save
  6. No changes needed in your solution
  7. No layering occurs in managed solutions

Your automation continues uninterrupted.

🎯 Final Thoughts

Certificate authentication is the gold standard for Power Automate → Dataverse integrations. It eliminates user dependency, avoids MFA issues, improves security, and ensures your flows run reliably for years.

If you’re building enterprise‑grade automation, this is the method you should be using.

Posted in Uncategorized | Tagged , , , , , , , | 2 Comments

How to Dynamically Create a Table in an Excel File Without Column Headers Using Microsoft Graph API and Power Automate?

Managing Excel files programmatically can be challenging, especially when the data is dynamic and lacks column headers. In this blog, we’ll walk through the process of dynamically creating a table in an Excel file without column headers using Microsoft Graph API and Power Automate. This approach ensures that you can work with dynamic data ranges efficiently.

Step 1: Identify the Used Range in the Excel Worksheet

The first step in creating a table dynamically is to determine the range of cells currently in use within the worksheet. Microsoft Graph API provides a convenient endpoint for this purpose.

Endpoint to Get Used Range:

graph.microsoft.com/v1.0/me/drive/root:/YourExcelFile.xlsx:/workbook/worksheets(‘{worksheet sheet name}’)/usedRange

Replace {YourExcelFile.xlsx} with the name of your Excel file and {worksheet sheet name} with the name of the worksheet you are targeting. Above root:/ means my excel file is under the root directory of my OneDrive. If your file under a sub-directory then you need to specify the sub-folder under the http end point like this:

root:/subfoldername/YourExcelFile.xlsx.

Step 2: Execute the HTTP Request in Power Automate

Using the Power Automate Azure Preauthenticated HTTP action step, execute the HTTP request to the above endpoint. This will return a JSON response containing details about the used range in the worksheet.

Example JSON Schema Returned:

{
“address”: “Sheet9!A1:C4”,
“formulas”: […],
“formulasLocal”: […],
“numberFormats”: […],
“values”: [[…], […], …]
}

Step 3: Extract the Address Value

From the returned JSON schema, focus on the address field. This field contains the range of cells that are currently in use, such as "Sheet9!A1:C4".

To extract the range using Power Automate expressions, you can use:

last(split(“Sheet9!A1:C4”, ‘!’))

This expression splits the string based on the '!' character, which separates the sheet name from the cell range, and then extracts the relevant range.

Step 4: Create the Table Using the Extracted Range

Once you have the used range, you can create a table by sending another HTTP request to the Microsoft Graph API.

Endpoint to Create Table:

graph.microsoft.com/v1.0/me/drive/root:/YourExcelFile.xlsx:/workbook/worksheets(‘{worksheet sheet name}’)/tables/add

Request Body:

{
“address”: “{address range}”,
“hasHeaders”: false
}

Replace {address range} with the range extracted from the usedRange call, and make sure hasHeaders is set to false since your Excel file does not have column headers.

Step 5: Retrieve the Table Name

After executing the table creation HTTP request, a JSON object will be returned. Look for the name property in this object, as it contains the name of the newly created table.

Step 6: Read the Excel Data Using the Table

Finally, with the table created, you can use the “List rows present in a table” action step in Power Automate to read the data from your Excel file. This step allows you to interact with the data dynamically within Power Automate.

Conclusion

By following these steps, you can dynamically create and manage tables in Excel files that lack column headers using Microsoft Graph API and Power Automate. This method provides a robust solution for handling dynamic data ranges and ensures that your automated workflows can interact with Excel files efficiently.

Check out my video tutorial regarding this blog.

Feel free to adapt these steps to fit your specific requirements, and you’ll have a powerful toolset for managing Excel data programmatically!

Posted in Uncategorized | Leave a comment