The Dataverse TDS endpoint

For many years, the only way to query Dataverse was through the Web API (OData) or the older SOAP service. Both are excellent for app integration but awkward for tools that speak SQL — Power BI's older DirectQuery patterns, SQL Server Management Studio for ad-hoc exploration, third-party reporting tools, SQL-aware ETL platforms. The TDS endpoint (Tabular Data Stream) gives Dataverse a SQL Server-compatible read-only endpoint.

What TDS is. TDS (Tabular Data Stream) is the protocol Microsoft SQL Server uses for client-server communication. Tools that speak TDS — SQL Server Management Studio (SSMS), Power BI Desktop, Excel's "Get Data from SQL Server", Azure Data Studio, any ODBC / JDBC driver using a SQL Server provider — can connect to the TDS endpoint as if it were a SQL database.

The endpoint. Each Dataverse environment exposes a TDS endpoint at a specific URL:

<environment>.crm.dynamics.com,5558

(port 5558 is the standard TDS port for Dataverse).

Connections authenticate with Microsoft Entra ID — typically via Entra ID password authentication or Entra ID interactive authentication. Service principals work for unattended scenarios.

What you can do.

• Run SELECT queries with full T-SQL syntax against any Dataverse table.
• Joins across tables using SQL JOIN syntax.
• Aggregations (GROUP BY, HAVING, SUM, COUNT, AVG).
• Window functions (ROW_NUMBER, RANK, etc.).
• Subqueries and CTEs.
• All the SQL syntax Azure SQL Database supports for read operations.

What you cannot do.

• No writes — TDS endpoint is read-only. INSERT / UPDATE / DELETE not supported.
• No DDL — can't create tables, indexes, or schema changes.
• No stored procedures.
• No transactional control — no BEGIN TRAN / COMMIT.
• Limited some SQL Server features — synonyms, change tracking statements, specific extensions don't apply.

Security. The TDS endpoint respects:

• User's security role — same as Web API access.
• Business unit scopes — same row-level filtering.
• Field-level security — secured columns are stripped from query results.
• Hierarchical security — same as Web API.

A user querying through the TDS endpoint sees only what they'd see through the standard Power Apps UI. The endpoint is a different protocol, not a different security context.

Power BI use cases. TDS is particularly valuable for Power BI:

• DirectQuery datasets can connect via the SQL Server connector to the TDS endpoint, querying Dataverse live.
• Import mode can refresh by SQL queries against the endpoint.
• Complex Power BI models can join across multiple Dataverse tables natively in SQL.

Compared to the OData connector, the SQL approach is sometimes faster for complex aggregations and joins.

Tooling for ad-hoc analysis. SQL Server Management Studio (SSMS) and Azure Data Studio connect to the TDS endpoint:

1. Connect to <environment>.crm.dynamics.com,5558.
2. Authenticate with Entra ID.
3. Browse the schema like any database.
4. Run SELECT queries interactively.

Useful for data exploration, ad-hoc analysis, troubleshooting, building queries for use in reports.

ETL integration. SQL Server Integration Services (SSIS) and similar SQL-aware ETL tools can extract from the TDS endpoint as a source. Combined with the read-only constraint, this is a one-way pattern — Dataverse out to other systems, not back.

Performance.

• Queries against the TDS endpoint go through Dataverse's security and audit pipeline; substantial complex queries take longer than equivalent queries against a raw SQL database.
• For very large analytical queries, Synapse Link for Dataverse (streaming data to OneLake) is the better path. The TDS endpoint is for moderate-volume direct-query needs.
• The TDS endpoint isn't a substitute for analytical data warehousing at scale.

Limits and caveats.

• Some columns are exposed differently — choice columns show as integer values; you might need to join to the metadata to get labels.
• Image and file columns behave specifically — typically not queryable directly.
• Calculated columns are computed in the query rather than pre-stored.
• Performance for very large tables can be slow because of security checks.
• Schema names may differ from intuitive expectations — verify with SSMS browsing first.

Common use cases.

• Power BI DirectQuery datasets for live Dynamics 365 reporting.
• SSMS for ad-hoc exploration by developers and analysts.
• SQL-aware reporting tools like Tableau, Qlik, Looker connecting via SQL drivers.
• ETL extraction for one-time data exports.

Common pitfalls.

• Heavy production reporting via TDS — for high-volume, complex analytics, use Synapse Link instead.
• Service-principal authentication confusion — Entra ID requirements can be fiddly; use Azure CLI or PowerShell to set up properly.
• Assuming SQL Server feature parity — TDS endpoint is read-only and doesn't support all SQL Server features.

Operational reality. TDS is a useful integration surface for SQL-aware tools and ad-hoc analysis. Don't make it the only analytical pattern; for serious analytics, layer Synapse Link / Microsoft Fabric on top.