---
title: "Test Dataverse"
description: "Test Dynamics 365 and Power Apps data in Dataverse through its TDS (SQL) endpoint — enabling the endpoint, Entra ID authentication, what the SQL can and cannot do"
url: "https://docs.justcat.it/how-to-guides/data-platforms/test-dataverse/"
---
# Test Dataverse


## 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:


**Properties**



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%;
  ```





**YAML**


```yaml
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:


**Properties**



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





**YAML**


```yaml
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: <a href="https://learn.microsoft.com/en-us/power-apps/developer/data-platform/how-dataverse-sql-differs-from-transact-sql" target="_blank">How Dataverse SQL differs from Transact-SQL</a>.
* Queries time out after **five minutes** (two for `SELECT *`, nested `FROM`s 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](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata"): one template over the list of entities or of option-set values.

## Related

* [SqlServer@2](https://docs.justcat.it/reference/data-sources/providers/sqlserver-2/ "SqlServer@2") — the provider, the Entra ID connection-string forms.
* [Work with secrets](https://docs.justcat.it/how-to-guides/organize-and-run-tests/work-with-secrets/ "Work with secrets") — the service principal's secret.