Use SQL to query CDS

A SQL connection to the Common Data Service’s Tabular Data Stream (TDS) endpoint is now available allowing you to execute SQL queries against the entity tables.

Enabling the TDS endpoint

This feature is still in preview and is not available in all regions and should not be used in production as it still subject to breaking changes.

The CDS environment must be version 9.1.0.17437 or above.

Open the Power Platform admin center, select your environment and click Settings.

Enable TDS 1

Click on Product and then click on Features.

Enable TDS 2

Set Enable TDS endpoint to On and click Save.

Enable TDS 3

Querying CDS with SQL

Open SQL Server Management Studio and click Connect. Enter your CDS environment URL followed by the port number 5558. Set the authentication to Azure Active Directory and enter your username and password. Click Connect.

Query CDS with SQL 1

You should be able to view and query the CDS tables.

Query CDS with SQL 2

Supported operations

The supported SQL operations include:

  • Batch operations
  • SELECT
  • Aggregation functions (i.e., Count() and Max() functions)
  • UNIONs and JOINs
  • Filtering

Limitations

INSERT and UPDATE operations will not work as the SQL connection is read-only.

The following CDS data types are not supported when using the SQL connection:

  • binary
  • image
  • ntext
  • sql_variant
  • varbinary
  • virtual
  • HierarchyId
  • managedproperty
  • file
  • xml
  • partylist
  • timestamp

Sources

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/b46a581a-39de-4745-b076-ec4dbb7d13ec

https://docs.microsoft.com/en-us/power-platform/admin/settings-features

All views expressed in my blog are my own and do not represent the opinions of any entity whatsover with which i have been, am now, or will be affiliated.