Reporting off Dataverse

What it does

Three broadly different ways to get numbers out: query the live environment through the TDS (SQL) endpoint, import into Power BI on a schedule, or replicate to an analytical store — Link to Fabric or Synapse Link — and report off that. Each moves the load somewhere different, and the interesting decision is when you stop querying live.

Key facts

The TDS endpoint is a Tabular Data Stream endpoint that emulates a read-only SQL connection over the Dataverse business layer. It honours the Dataverse security model, so users only see what they can see. It's enabled by default.
Read-only means read-only. INSERT and UPDATE don't work. Ports 1433 and/or 5558 need to be open on the client, and only Microsoft Entra ID authentication is supported — no SQL auth, no Windows auth.
There's no hard result size limit any more, but there is a fixed five minute timeout. Queries containing SELECT *, nested FROMs or JOINs automatically get cut to two minutes because of the load they put on the server.
Unsupported data types on the SQL connection include binary, image, varbinary, virtual, file, xml, partylist, timestamp and choices. Virtual and audit table types aren't supported. Neither are elastic tables.
Choice columns are flattened into a name and a label column. The label is stored separately, can't be indexed and is expensive to return — aggregate and filter on the value, not the label. Microsoft suggests keeping to under 10 labels in a query.
Keep queries under about 100 columns, always use a TOP clause, and don't use the NOLOCK hint — it stops Dataverse optimising the query.
SQL queries don't fire plug-ins registered on RetrieveMultiple or Retrieve, so any query or result rewriting those plug-ins normally do simply doesn't happen.
TDS queries run under the service protection API limits like everything else.
Dates come back as UTC. That changed from local time at some point and old reports carry the assumption.
For anything that can't finish in five minutes, Microsoft's own guidance is to stop and use a data integration path — Link to Fabric, Synapse Link or dataflows.

When to use / skip

Use the TDS endpoint for exploration, for small operational reports, and for the "I just need to check something" queries that would otherwise become a support ticket. Use Power BI import for departmental reporting where yesterday's data is fine. Move to Link to Fabric the moment any of these are true: the report takes more than a couple of minutes, more than a handful of people run it, it joins across a lot of tables, or it's on a schedule that overlaps with business hours. Live querying a production Dataverse environment for analytics is borrowing capacity from the people using the app, and the five minute timeout is the platform telling you so. The tell I use is the first time someone asks "can we make the report faster" — that's the replication conversation, not a tuning conversation.

Configuration decisions

Whether the TDS endpoint stays on at all, and whether to turn on user-level access control so that only users with the Allow user to access TDS endpoint privilege can reach it.
Import versus DirectQuery in Power BI, and if DirectQuery, whether the report shape survives the timeout.
Where the analytical copy lives — OneLake via Link to Fabric, or your own storage via Synapse Link.
Which security model applies to the analytical copy. TDS inherits Dataverse security; a lakehouse does not, and someone has to design that.
Whether reports need row-level security replicated downstream, which is usually the hidden cost of moving off live queries.

Gotchas

Plug-ins not firing on TDS queries is the sharp one. If a client uses RetrieveMultiple plug-ins to filter or reshape data — and plenty do for security reasons — a SQL query bypasses that entirely. That's a data exposure question, not a performance one.
The timeout dropping from five minutes to two because a query contains a JOIN surprises everyone. The report worked, someone added a join, now it fails.
Choice label columns look free and aren't. A report grouping on ten choice labels is doing ten expensive lookups per row.
Port redirection from 1433/5558 to 443 by a web proxy breaks TDS in ways that look like an authentication problem. It's fixed by allowlisting IP addresses, not hostnames.
Elastic tables aren't reachable through TDS at all, which catches out anyone who adopted them for volume and then wanted to report on them.

Consultant notes

Turn on user-level TDS access control on any environment holding sensitive data. Enabled-by-default plus Entra ID auth means anyone with SSMS and a role can read what their role permits, which is correct but rarely what the security team pictured.
When a client asks for "a SQL connection to Dataverse", find out whether they mean exploration or a nightly ETL. The first is fine, the second is a replication project.
Put the five minute timeout in the design constraints document. It's the cleanest objective line between "report" and "data platform", and having it written down settles arguments later.
Budget for reimplementing security downstream whenever you move reporting off live queries. It's the piece that gets forgotten and it's the piece that gets audited.

Revisit if the TDS timeout behaviour changes, or if elastic tables become queryable through the endpoint.