Grounding Copilot Studio and custom agents on clean tables, not raw lists

Last updated 26 October 2026

A Copilot Studio agent pointed at a SharePoint list answers lookups as IDs, miscounts once the list passes a couple of thousand rows, and cannot join one list to another.

A lookup column on a raw SharePoint list is an ID, and an agent grounded on that list answers with the ID. Ask a help desk agent “who owns the printer outage ticket” and it replies with the number of an entry in the Owners list. Ask how many tickets are tagged “network” and it gives a figure it cannot back up, because the tags sit in a multi-choice field the index reads as one string. Fixing either means custom logic in the agent for every kind of question.

The service desk manager who built that agent usually tried the obvious thing first. She added the Tickets list as knowledge in Copilot Studio and tried the five questions she asks every week. The answers looked right. A month later the list had grown and the counts stopped matching the list view.

Why not point Copilot Studio at the list

You can, and Microsoft supports it. The Learn article Add SharePoint as a knowledge source, checked on 23 October 2026, says the lists you select are ingested into Dataverse and indexed to improve search results, and that queries use current data with the user’s own SharePoint credentials. The limits sit in the Copilot Studio quotas and limits article, checked the same day. List queries only return data from the first 2,048 rows. An agent takes up to 15 lists, 35,000 rows a list and 120,000 rows in total, and the knowledge article advises keeping to 10 lists. You cannot pick a list view, and a list whose default view has more than 12 lookup columns is not supported.

The line about the first 2,048 rows is the one that matters for a help desk. A Tickets list of 14,000 rows is well inside the ceiling, so Copilot Studio accepts it without a warning. Every answer then comes from a slice of the list, and nothing in the reply tells the user so.

A SharePoint site added as knowledge behaves differently again. The knowledge sources summary says the site source uses Graph search to return results, and generative answers then summarise what came back into one reply. That is retrieval. It is good at finding the policy page that explains escalation, and poor at “how many”, because the model can only count what the search handed it.

The map of the Microsoft AI stack for SharePoint lists sets list knowledge beside Copilot connectors, Work IQ, Agent Builder and MCP, with each one’s published limit.

The Power Automate action workaround and what it costs

The usual fix is a flow added to the agent as a tool. The agent passes the question in, a prompt turns it into an OData filter for the SharePoint Get items action, and the flow returns the matching rows. It works in a demo, and on a list of a few hundred items it keeps working.

The cost grows with the list. The filter is keyword and field matching, so “VPN tickets” finds the tag and every description that mentions a VPN in passing. Every question is a flow run, and the quotas article notes that flows an agent triggers consume Power Platform requests from the same allowance as the rest of your automation. There are no joins. A question about tickets whose category has a resolution target under eight hours needs a second Get items call on Categories and logic in the flow to match the two, so each new kind of question means editing the flow.

The prompt that writes the filter also holds a copy of the list’s columns in plain text. Rename a choice value from “P1” to “Critical” and the prompt goes out of date with no error anywhere, and the agent starts reporting “no tickets found”.

What breaks as the list grows

Somewhere in the low thousands of items both routes start to fail in ways that are easy to miss. The knowledge source answers from the first 2,048 rows. The flow route hits the 5,000-item list view threshold the first time it filters on a column nobody indexed, and returns an error the agent turns into an apology. Averages and totals need every matching row, so the flow has to page through all of them and hand the lot to the model to add up, which is the counting problem again one step further down.

Ground the agent on resolved tables

SPSync replicates the lists an agent should ground on into a SQL database and refreshes them on a schedule. It only reads from SharePoint. The agent reads the database, where the joins already work.

The owner question becomes a join, because the lookup is a key column into the Owners table. The tag question becomes a count over a child table with one row per ticket and tag. Deleted tickets keep their row with sp_deleted_at set, so the agent can still answer about a ticket closed and removed last week. Some grounding setups prefer one flat table per list, and SPSync can generate reporting views that add person names to each row.

There are two ways to put that database in front of a Copilot Studio agent today. Azure SQL and Azure AI Search both appear in the list of Power Platform real-time knowledge connectors on Learn, a preview feature as of 23 October 2026. For those, Microsoft indexes only table and column names, and each question runs against the database at runtime with the signed-in user’s token. For PostgreSQL or SQL Server, or an agent built outside Copilot Studio, give the agent a read-only SQL tool over the replica, or an MCP server pointed at it, covered below. SPSync runs no agent.

How does the agent answer a count and a cross-list question?

Take “how many open high-priority tickets are tagged network”. Against the replica the agent writes one query: count rows in tickets where priority is High and status is not Closed, joined to tickets__tags where the tag is “network”, leaving out rows with sp_deleted_at set. The database looks at all 14,000 rows, where the knowledge source stopped at 2,048. The answer matches the list view, and you can paste the query into a SQL client and check it yourself.

The cross-list question is “which owners hold open tickets in categories with a target under eight hours”. The agent joins tickets to categories on the category key and to owners on the owner key, filters on the target column, and groups by owner. On the list, that question needs two knowledge sources and a model guessing at the link between them. The replica turns it into a join the database already knows how to run.

Azure AI Search fits a different question. Use it when the agent should find tickets that describe the same fault in other words, and keep counts and joins on the SQL side.

Copilot Studio Azure SQL knowledge not retrieving data

The failure people report runs like this. The Azure SQL knowledge source connects, the preview in Copilot Studio shows rows, and the agent then says it cannot reach the data, or answers from somewhere else. A Microsoft Q&A thread from December 2025, Azure SQL knowledge source connected but agent cannot retrieve data, describes exactly that with a five-row test table. A Microsoft Q&A moderator’s answer there says the preview only validates the connection, and that at runtime the connector relies on semantic grounding rather than live conversational SQL. Learn’s real-time connectors page puts it in its own terms: Microsoft indexes only metadata, such as table and column names, and each request is executed against the database at runtime. Read together, the agent works out which table and columns a question is about from their names, then queries them.

These are the checks we would run, in this order:

• Keep the agent and the database in one tenant. The person who asked that question later reported that Microsoft support pointed them to documentation saying a database in another tenant is not supported, even when you connect with an account from that tenant.
• Test as someone who can read the tables. Each runtime call uses the signed-in user’s own token, so a maker’s admin login proving the connection says nothing about a team lead with no database user. Learn’s troubleshooting page for enterprise knowledge sources lists this as Forbidden access.
• Check the network. The same page lists firewall errors, and a Bad gateway error when Deny Public Network Access or gateway settings block the connection.
• Ask for data. Learn says a content moderation check rejects questions that search for text inside records, ask to change data or ask how to do something, and gives no detailed error when it does. “How many high-priority tickets are open?” passes, and “find tickets that mention VPN” is the kind it rejects.
• Give it narrow, well-named tables. A database holding every list from a site gives the agent the most room to pick the wrong table. Point the source at a few views with plain names, such as open_tickets with category, owner, priority and days_open, and write a description that says what a ticket is.

If per-user sign-in is the sticking point, a service principal connection is worth a test. The SQL Server connector offers a Service principal (Microsoft Entra ID application) connection type, which is shareable. Learn’s real-time knowledge page describes runtime calls made with each user’s token and does not say how a service principal connection behaves there, so try it with a tester who has no database access of their own before you rely on it. If it works, the database sees one application. Grant that application’s database user read-only access to the agent’s views, and expect per-person row-level security to stop applying.

Can a Copilot Studio agent use an MCP server over the replica?

Copilot Studio can call tools on a Model Context Protocol server, which opens a route for databases the real-time connector list does not cover, such as PostgreSQL or SQL Server in your own data centre. Learn’s MCP overview for Copilot Studio, checked on 23 October 2026, says the agent needs generative orchestration turned on and supports MCP tools and resources. You add the server on the agent’s Tools page with the MCP onboarding wizard: a name, a description the orchestrator reads to decide when to call it, the server URL, and API key or OAuth 2.0 authentication. The page on connecting an existing server says Copilot Studio supports the Streamable HTTP transport only, having dropped SSE after August 2025, so the server has to run at a URL Copilot Studio can reach.

For SQL Server and Azure SQL, Microsoft’s own SQL MCP Server, part of Data API builder from version 1.7, suits a replica. A JSON configuration lists the tables and views it may expose, with permissions per role, and it serves Streamable HTTP. By design it does not turn questions into free-form SQL. Its tools include describe_entities, read_records and aggregate_records, and it builds the query itself. Give the agent’s role read permission only, since nothing should write to the replica.

For PostgreSQL, pick a Postgres MCP server that serves Streamable HTTP and connect it with a login that holds only the read-only role. Many database MCP servers are written for local use over stdio, so check the transport before you build around one.

The trade against the Azure SQL connector is identity. With an API key, every question reaches the database as the server’s own login, so the database cannot tell one team lead from another. Row-level security per person needs OAuth 2.0 and a server that carries the user’s identity through to the database. SPSync’s own spsync mcp serve reads under one database role and has no sign-in of its own, so it does not carry a person’s identity either; SPSync’s part is keeping the tables current.

What to replicate

Include the lists the main list looks up into, here Owners and Categories alongside Tickets, or the joins come back empty. Replicate to a database Copilot Studio or your agent framework can query, such as Postgres, SQL Server or Azure SQL; the pattern works fully on those relational destinations today. Match the schedule to how quickly tickets change, then give the agent a database login that can only read those tables or views. SharePoint item permissions are not copied, so the database role and row-level security decide what the agent sees. For an agent built outside Copilot Studio, where Copilot’s own controls do not apply, the governance resource for custom agents sets out the login, views and audit log to put in place.

If the agent also needs retrieval by meaning, Postgres with pgvector and Azure AI Search are the shipped destinations that carry vectors. Elasticsearch, OpenSearch, LanceDB, Qdrant and Weaviate are shipped too, for keyword and filtered search; they carry the replicated columns and no vector.

The article on why Copilot cannot count the rows in a SharePoint list goes through the same limits with a help desk list moved into Azure SQL, and the queries an agent runs against it. It also covers the Days Open calculated column, which SharePoint only works out when an item is saved.

When the complaint is a single item the agent insists does not exist, see Copilot Studio says it cannot find the item that is row 3,000, which includes a test list and six prompts that show which rows your own agent can reach.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name copilot_studio_grounding --prefix copilot_studio_grounding --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name copilot_studio_grounding --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: copilot_studio_grounding
prefix: copilot_studio_grounding
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: copilot_studio_grounding
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist