Get Help

Test Dataverse

Dataverse — the database behind Dynamics 365 and Power Apps — exposes a read-only SQL endpoint. CAT queries it like SQL Server with the SqlServer@2 provider, so CRM data can be tested, and compared with the warehouse it feeds, without an export.

Prerequisites

  • The environment’s TDS endpoint is enabled — Power Platform admin center → Environments → the environment → Settings → Product → Features → Enable TDS endpoint. It is on by default; an administrator may have turned it off. With user-level control on, the account also needs the Allow user to access TDS endpoint privilege.
  • Microsoft Entra ID authentication only — your own account (MFA prompts included) interactively, a service principal with access to the environment for unattended runs. SQL and Windows authentication are rejected.
  • Outbound TCP 1433 (or 5558) from the machine that runs CAT to <organization>.crm.dynamics.com (the region suffix varies — crm4, crm11, …).

Add the data source

The server is the environment’s URL; there is no database to name. CAT Studio: technology Dataverse. YAML:

Name
crm
Provider
SqlServer@2
Connection string
Server=contoso.crm4.dynamics.com;
Authentication=Active Directory Service Principal;
User Id=%DATAVERSE_CLIENT_ID%;
Password=%DATAVERSE_CLIENT_SECRET%;

Data sources:
- Name: crm
  Provider: SqlServer@2
  Connection string: >
    Server=contoso.crm4.dynamics.com;
    Authentication=Active Directory Service Principal;
    User Id=%DATAVERSE_CLIENT_ID%;
    Password=%DATAVERSE_CLIENT_SECRET%;

Interactive instead: Authentication=Active Directory Interactive and no user/password. If only port 5558 is open, Server=contoso.crm4.dynamics.com,5558.

Write the tests

Tables are the Dataverse logical names — account, contact, opportunity; a lookup column comes as <lookup>id and <lookup>name, a choice column as value and label:

Name
Every active account has an owner
Suite
CRM data quality
Data source
crm
Query
SELECT TOP 50 accountid, name FROM account WHERE statecode = 0 AND ownerid IS NULL
Expectation
set is empty
Name
Warehouse customers equal CRM accounts
Suite
CRM vs warehouse
First data source
crm
First query
SELECT accountid, name FROM account WHERE statecode = 0 ORDER BY accountid
Second data source
DWH
Second query
SELECT SourceId, CustomerName FROM dim.Customer WHERE SourceSystem = ‘CRM’ AND IsActive = 1 ORDER BY SourceId
Expectation
sets match
Key
1
Tests:
- Name: Every active account has an owner
  Suite: CRM data quality
  Data source: crm
  Query: SELECT TOP 50 accountid, name FROM account WHERE statecode = 0 AND ownerid IS NULL
  Expectation: set is empty

- Name: Warehouse customers equal CRM accounts
  Suite: CRM vs warehouse
  First data source: crm
  First query: SELECT accountid, name FROM account WHERE statecode = 0 ORDER BY accountid
  Second data source: DWH
  Second query: SELECT SourceId, CustomerName FROM dim.Customer WHERE SourceSystem = 'CRM' AND IsActive = 1 ORDER BY SourceId
  Expectation: sets match
  Key: 1

Good to know

  • The endpoint is read-only and speaks a subset of T-SQL; SELECT with joins, GROUP BY, TOP, aggregates — yes; no temp tables, no INSERT. Details: How Dataverse SQL differs from Transact-SQL.
  • Queries time out after five minutes (two for SELECT *, nested FROMs and heavy joins) — select the columns you need, TOP what you can, and let Key + ORDER BY do the comparison, not the query.
  • Dates come back in UTC. Choice labels are expensive — filter and compare on the value column.
  • Dataverse is also a classic source for generated tests: one template over the list of entities or of option-set values.
← Test Microsoft Fabric
Test CSV and Excel files →