Dataverse SQL queries from Power Automate and Copilot Studio

September 23, 2026

The Dataverse Web API accepts a read-only T-SQL subset through the sql query option. Dataverse SQL puts a usable surface on it: typed actions for flows and apps, and an MCP endpoint that gives a Copilot Studio agent discovery tools so it writes SQL against columns it has confirmed exist.

Two rough edges the connector absorbs

Calling the sql option directly means dealing with both of these yourself.

The entity set in the URL must match the base table of the query. A query beginning FROM account has to go to /accounts, and FROM msdyn_botsession to /msdyn_botsessions. The connector reads the FROM clause, resolves the entity set from table metadata, and builds the URL. You write the table name and never the entity set.

The supported subset is narrow, and a rejected statement returns a raw fault. Every statement is checked locally first. Anything unsupported comes back as a named issue — select_star, having, unsupported_join — describing what to write instead, with no round trip to Dataverse.

Everything is read-only. A statement must be a single SELECT, and write keywords are rejected before the request is built.

Seven visible operations

An eighth operation, Get row schema, is marked internal and backs the dynamic content below.

Execute SQL takes the statement, a page size, an optional row budget, and a switch for formatted values. Formatted values add display text for choice, lookup, and date columns as @OData.Community.Display.V1.FormattedValue annotations next to the raw values.

Column names come from a sample row, not a prediction

The designer resolves the columns your statement returns and offers them as typed dynamic content, so you pick name or revenue directly instead of writing item()?['name']. Types come from Dataverse metadata for whichever table each alias refers to, so in a join c.birthdate is typed from contact rather than the base table. Aggregates are typed from their function: COUNT(*) is a whole number, SUM() a decimal, and MIN()/MAX() inherit the type of the column they wrap.

Two behaviors shape how that works.

Alias your aggregates. Dataverse names an aggregate column after its alias, so SELECT COUNT(*) AS total produces a total field while a bare SELECT COUNT(*) produces a field this connector cannot name and therefore omits.

Dataverse also decides what comes back, not your SELECT list. A plain query returns the primary key and @odata.etag on top of what you asked for, and some tables add more — systemuser and team return ownerid unselected, while queue and role do not. An aggregate query returns only its aliased columns. That pattern isn’t derivable from metadata, so the connector runs your statement once for a single row at design time and reads the real column names:

var sampled = await SampleRowAsync(table, validation.Statement).ConfigureAwait(false);

An empty table has no row to inspect, so the schema falls back to the SELECT list plus the primary key and etag, and the response reports sampled: false.

Resolution happens at design time against the statement in the box. Build the statement from an expression and there’s nothing to inspect yet, so rows stays untyped. The action still runs normally.

Six tools for an agent

Add the connector as a tool in Copilot Studio and the MCP endpoint exposes:

The tool descriptions carry the supported subset, so an agent generally writes a valid statement on the first attempt. When it doesn’t, failures come back as tool results rather than protocol errors, and the agent reads the issues and valid values and retries instead of stalling. An unknown table name returns near matches from your environment.

The intended path is list_tables → describe_table → run_sql. An agent that skips discovery and guesses column names gets corrected by unknown_column, but those round trips are avoidable.

What the subset covers

Out of scope: SELECT *, subqueries, CTEs, HAVING, UNION, RIGHT JOIN, FULL JOIN, CROSS JOIN, CASE, COALESCE, window functions, and string, date, or math functions beyond the two above. WHERE cannot compare two literals or two columns, and cannot apply a function to a column value. ORDER BY and GROUP BY take columns only, including date parts, so GROUP BY MONTH(createdon) is rejected.

A statement that fits looks like this:

SELECT a.name, COUNT(*) AS contact_count
FROM account AS a
INNER JOIN contact AS c ON a.accountid = c.parentcustomerid
WHERE a.createdon >= DATEADD(day, -30, GETUTCDATE())
GROUP BY a.name
ORDER BY a.name

Paging without a loop

Set the page size on Execute SQL and it becomes Prefer: odata.maxpagesize. When more rows exist, hasMore is true and nextLink carries the continuation — pass it to Get next page unchanged. Next links are checked against your environment before they’re followed.

Get next page returns the same shape, but sql, table, and entitySet come back empty. A next link carries the continuation token, not the statement that produced it, so carry those values forward from the first call if you need them.

To collect several pages in one action, set Maximum rows. The connector follows next links itself until it has that many rows, and stoppedBecause reports which limit ended the run:

The final page may carry the total slightly past Maximum rows, because pages are never split. Whenever paging stops with rows remaining, nextLink still comes back, so a flow resumes exactly where it left off. Leave Maximum rows at 0 for a single page.

TOP and OFFSET … FETCH are accepted but flagged with a warning. The Dataverse reference lists them as supported syntax while its paging guidance says they aren’t honored. Prefer the page size, or page by filtering on the last id you saw:

SELECT name, accountid
FROM account
WHERE accountid > '00000000-0000-0000-0000-000000000000'
ORDER BY accountid

Throttling that outlasts the script

Dataverse enforces service protection limits by returning 429 with a Retry-After header. The connector waits and retries automatically when that wait is short.

The wait scales with how demanding the previous five minutes were, so it can exceed the two minutes a connector script is allowed to run. When it does, the call returns a throttled error carrying retryAfterSeconds rather than being killed mid-wait. Handle it in a flow with a Delay of that many seconds followed by a retry.

Other ceilings worth knowing:

For the aggregate limit, add a filter — typically a date range or a subset of a choice column — and combine the results of several runs.

Deploy it

Register an app in Microsoft Entra ID, add the Dynamics CRM → user_impersonation delegated permission, grant consent, and create a client secret. Add https://global.consent.azure-apim.net/redirect as a web redirect URI.

Then point the files at your environment. In apiDefinition.swagger.json, set host to your environment hostname. In apiProperties.json, replace [YOUR_CLIENT_ID] with your application ID and replace https://org.crm.dynamics.com in both AzureActiveDirectoryResourceId and resourceUri with your environment URL. Leave redirectUrl as [YOUR_REDIRECT_URL], since the platform substitutes the real value at deploy time.

pac auth create --environment <your-environment-url>

pac connector create `
  --api-definition-file apiDefinition.swagger.json `
  --api-properties-file apiProperties.json `
  --script-file script.csx

pac has no parameter for the client secret. Paste it in Power Apps → Custom connectors → Dataverse SQL → Edit → Security, or add it as clientSecret inside oAuthSettings in a temporary copy of apiProperties.json, deploy from that copy, and delete it afterwards. Never put a secret in the file you keep.

The platform generates a redirect URL unique to this connector and environment. Read it back and register it:

pac connector download --connector-id <connector-id> --outputDirectory ./verify

The value sits at properties.connectionParameters.token.oAuthSettings.redirectUrl. It encodes the publisher prefix, the connector name, and the environment, with characters escaped — _ becomes -5f and a space becomes -20:

https://global.consent.azure-apim.net/redirect/new-5fdataverse-20sql-5f<ENVIRONMENT_SUFFIX>

Copy the whole value exactly as downloaded rather than assembling it by hand, and add it to the app registration alongside the generic one. OAuth consent fails until it’s registered, and a second environment produces a different URL that also needs registering.

Create a connection, then run List tables with no parameters. A list of your tables confirms auth, host, and script are all wired up.

When something comes back wrong

Telemetry is off by default. Set APP_INSIGHTS_ENABLED to true in script.csx and replace APP_INSIGHTS_KEY with your instrumentation key to record executed statements, MCP tool calls, validation failures, and unhandled errors. Telemetry failures never fail an operation.

Resources

• Dataverse SQL connector source
• Use SQL to query data with the Dataverse Web API
• Page results
• Service protection API limits
• Query anti-patterns
• Write code in a custom connector