Azure AI Foundry agents and SharePoint list rows

23 October 2026

Tags: Relational databasesSearch and vector storesAIDesktopCommand line

The PMO built its first Foundry agent in an afternoon. It has a prompt, a model and the SharePoint tool pointed at the PMO site. Asked “what does our risk policy say about escalating a critical risk?”, it quotes the right paragraph of the risk management policy and cites the Word document. Then the programme director asks it “how many critical risks are open on Project Falcon, and who owns them?” and the agent says it cannot find any, or it answers with another paragraph from the same policy.

The risks are not hidden. They sit in the RAID log, a SharePoint list called Risks with about 3,800 items on the same site, with a Project lookup, a Severity choice, an Owner person column and a Status. The director can filter it to the answer in the browser in a few seconds. This post explains why the Foundry SharePoint tool does not reach those rows, what each Foundry tool reads as of 23 October 2026, and how to give the agent the list as a table it can query.

Why does an Azure AI Foundry agent with the SharePoint tool miss list rows?

Because the tool is built for documents. Microsoft Learn’s page on the SharePoint tool for Foundry Agent Service, updated on 21 October 2026, marks it as preview and says the agent “can invoke the SharePoint tool to retrieve relevant text from documents the user can access”. The tool sits on the Microsoft 365 Copilot Retrieval API, and Learn’s limitations list says semantic and hybrid retrieval cover .doc, .docx, .pptx, .pdf, .aspx and .one files. List items are not mentioned anywhere on the page.

The numbers rule it out for row questions even if an item came back. The Retrieval API returns at most 25 results per call and allows 200 requests per user per hour, with a query of up to 1,500 characters. A count of open critical risks across 3,800 items needs every row with a matching Severity and Status, and 25 text extracts ranked by relevance cannot give you that. A search ranks; a count needs a filter over every row.

Learn adds four conditions that matter when you plan around the tool. It runs only under the signed-in user’s identity, with no app-only or service principal option. The site and the agent must be in the same tenant. An agent can have one SharePoint tool. And the tool does not work when the agent is published to Microsoft Teams. None of these is a problem for the policy question, which is what the tool is good at.

Microsoft now titles the product Microsoft Foundry on Learn. Most people still search for Azure AI Foundry, and both names mean the same service here.

What each Foundry tool reads, on 23 October 2026

The toolbox overview lists every tool Foundry Agent Service supports. These are the ones that come up when the data is a SharePoint list, with the status each Learn page shows today:

There is no SQL tool in the list. Every route to rows in a database goes through MCP, a function you write, or an OpenAPI description of an API you host. The Fabric data agent tool page, updated on 14 October, says the tool is scheduled for deprecation on 6 November 2026 and points to Fabric IQ, which is in preview, so do not start a new design on the older tool.

If you are weighing Foundry against Copilot Studio, Agent Builder or Work IQ, our map of the Microsoft AI stack for SharePoint lists sets those surfaces side by side. This post stays with Foundry.

Does Microsoft Agent Framework have a SQL tool?

No built-in one. Learn’s Agent Framework tools page (last updated on 29 September 2026) names function tools, code interpreter, file search, web search, hosted and local MCP tools, the Foundry toolbox and shell tools. A database is reached through one of two of those. A function tool is a Python or C# function that runs a query you wrote, with the parameters the model fills in. An MCP tool connects to a database MCP server, which describes the tables and runs the reads.

Either needs the list to be in a database first. Pointing a function at Microsoft Graph instead brings back every problem the database avoids: paging through the whole list for any question that touches every row, lookup columns that come back as ids, the 5,000-item list view threshold on filtered queries, and an app registration with Graph permissions inside your agent.

Putting the list in Azure SQL, step by step

SPSync replicates SharePoint lists one way into Azure SQL or Azure Database for PostgreSQL, among other destinations, and keeps the tables current on a schedule. SharePoint stays the system of record, and SPSync never writes to it. In the desktop app, connect the PMO site, tick the Risks list and the Projects list it looks up to, pick Azure SQL, and check the schema preview. From the command line:

spsync export-config --output pmo.yaml --pipeline-name pmo --prefix pmo \
  --destination-kind mssql --connection-string-env PMO_DB_URL \
  --dataset-name pmo --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/pmo \
  --list Risks --list Projects
spsync run pmo.yaml

After the first load the pmo schema holds a table for each list, keyed by sp_item_id. The Project lookup is stored as project_id and project_value, Owner as owner_user_id, which joins to the shared sp_users table, and a deleted item keeps its row with sp_deleted_at set. Later runs read only what changed, so a short schedule keeps the table a few minutes behind the list, and a full reconcile, weekly by default, checks the table against the list. The schema reference shows how every column type maps.

Then write the view the agent will read. Keep the column names plain, because the model reads them:

CREATE SCHEMA agent;
GO
CREATE VIEW agent.open_risks AS
SELECT r.sp_item_id, r.title AS risk, r.project_value AS project,
       r.severity, r.status, u.display_name AS owner,
       r.sp_modified AS last_changed_in_sharepoint
FROM pmo.pmo_risks AS r
LEFT JOIN pmo.sp_users AS u ON u.id = r.owner_user_id
WHERE r.sp_deleted_at IS NULL AND r.status <> 'Closed';
GO
CREATE USER risk_reader WITH PASSWORD = '<a long generated password>';
GRANT SELECT ON SCHEMA::agent TO risk_reader;

The risk_reader login can read the view and nothing else in the database. That login, and the view, are the security boundary for everything that follows.

Exposing the replica to the agent through SQL MCP Server

Microsoft’s SQL MCP Server is part of Data API builder from version 1.7. It exposes the tables and views you configure as seven MCP tools, such as describe_entities, read_records and aggregate_records, and builds the SQL itself from structured parameters. Learn says it intentionally does not support NL2SQL, so the model never writes free-form SQL against your database. It works with SQL Server and Azure SQL, PostgreSQL, MySQL and Cosmos DB, so the same steps apply to an Azure Database for PostgreSQL replica with --database-type postgresql.

Two details from Learn shape the setup. read_records reads one table or view and does not join, which is why the view above does the join. And aggregate_records, the tool that counts, arrived in version 2.0, so on 1.7 the agent has to read every matching row to count them. Use 2.0 or later.

These snippets, and the Foundry agent code further down, follow the syntax on the Learn pages linked here. We have not run them against a live Foundry project, so check them against the current SDK and Data API builder releases before you rely on them.

dab init --database-type mssql --connection-string "@env('RISK_READER_DB')" \
  --config dab-config.json
dab add OpenRisks --source agent.open_risks --source.type view \
  --source.key-fields "sp_item_id" --permissions "anonymous:read" \
  --description "Open risks from the PMO RAID log, one row per SharePoint item, a few minutes behind SharePoint"
dab configure --runtime.mcp.enabled true
dab configure --runtime.mcp.dml-tools.create-record false
dab configure --runtime.mcp.dml-tools.update-record false
dab configure --runtime.mcp.dml-tools.delete-record false
dab configure --runtime.mcp.dml-tools.execute-entity false
dab start --config dab-config.json

The entity grants read only, the write tools are switched off, and the login behind the connection string can only select from one view, so a misbehaving prompt has three layers to get through and no write path at the end. The anonymous role is fine on your own machine. Before the endpoint is reachable from Foundry, put it behind authentication, which the next step uses.

How do you connect the MCP tool to a Foundry agent?

Foundry Agent Service calls a remote MCP endpoint, so host the server where Foundry can reach it. Learn’s MCP tool page supports public endpoints with any agent setup, and private endpoints only with the Standard agent setup on your own virtual network, where Azure Container Apps on a dedicated subnet is the tested host. Store the server’s key or token in a project connection, then create the agent:

from azure.identity import DefaultAzureCredential
from azure.ai.projects import AIProjectClient
from azure.ai.projects.models import PromptAgentDefinition, MCPTool

with AIProjectClient(endpoint=PROJECT_ENDPOINT, credential=DefaultAzureCredential()) as project:
    risks = MCPTool(
        server_label="pmo_risks",
        server_url="https://risks-mcp.example.net/mcp",
        allowed_tools=["describe_entities", "read_records", "aggregate_records"],
        require_approval="never",
        project_connection_id="risks-mcp-connection",
    )
    project.agents.create_version(
        agent_name="pmo-assistant",
        definition=PromptAgentDefinition(
            model="gpt-5-mini",
            instructions=(
                "Answer questions about risks from the OpenRisks entity. "
                "Count with aggregate_records. Quote last_changed_in_sharepoint "
                "and the sp_item_id for each risk you name."
            ),
            tools=[risks],
        ),
    )

allowed_tools keeps the agent to the three read tools even if someone switches a write tool back on in the server, and the three tools only read, which is why approval can be never. Keep the SharePoint tool on the same agent for the policy questions. If you build in code with Agent Framework instead, MCPStreamableHTTPTool takes the same URL, as Learn’s MCP tools page for Agent Framework shows.

Ask the director’s question again. With those instructions the agent has what it needs to call aggregate_records with a filter on project and severity, then read_records for the owners, and to answer from rows. Check the tool calls in the playground the first few times. Learn’s limitations give a non-streaming MCP call 100 seconds, so keep the view simple enough to answer well inside that.

When a search index fits the question better

Some questions about the RAID log are about wording: “which risks mention the supplier’s insolvency?” A filter cannot answer that well. SPSync’s Azure AI Search destination writes the same lists into an index, resolves lookups, and can generate embeddings on sync, so only changed items go back to the embedding model. Attach that index to the agent with the Azure AI Search tool, which carries no preview label, or wrap it in a Foundry IQ knowledge base as a search index knowledge source. Learn’s knowledge source page asks for a semantic configuration on that index. SPSync does not create one, and it keeps any you add in the portal. Our post on SharePoint lists in Foundry IQ after On Your Data retired sets up that knowledge source step by step, vectorizer included.

Foundry IQ also has its own routes to SharePoint, and Learn marks every one of them as preview: remote SharePoint, indexed SharePoint, and an Azure SQL source that indexes a table or view. The indexed route uses the SharePoint in Microsoft 365 indexer, whose list support is in preview from the 2026-05-01-preview REST API. These are worth testing if you need SharePoint permissions trimmed at query time, which a replica does not give you. They still answer through search, so a count goes to the SQL route.

What the replica does not carry

Three things stay in SharePoint. Item-level permissions are not copied, so the database login and the views decide what the agent reads, and row-level security is the tool if the Falcon team should see only Falcon risks. File contents are not copied either: SPSync replicates list columns and library metadata, so the risk policy itself stays with the SharePoint tool. And the replica is a few minutes behind, which is why the agent’s instructions ask it to quote the last change time.

SPSync ships no agent. The view, the MCP server you host for Foundry and the agent are yours, and the same tables can feed a Power BI report or a Power App as well. The licensing page explains how SPSync is licensed for each installation and destination family.