Yes, you heard right: The new “Tabular Data Stream” (TDS) protocol allows you to run SQL queries on Dynamics online (CDM). Currently the TDS endpoint is in preview and only allows read only operations, but this will change in the future according to MS.
The following operations are available right now:
- SELECT
- Aggregation functions (i.e., Count() and Max() functions)
- UNIONs and JOINs
- Filtering
To try it out by yourself, you must activate the TDS preview:
- Browse admin.powerplatform.microsoft.com
- Select your instance
- Go to preferences
- Open Product ? Features
- Find and activate TDS
Now open SSMS on your PC (minimum version supported is v18.4).
For server name you must use your Dynamics online URL + Port 5558
Server Name <YourName>.crm{x}.dynamics.com,5558
For Authentication only Azure AD is supported.
You can use this connection via ConnectionString (even though MS says you cannot in preview). Use a connection string like this:
Server=<YourName>.crm{x}.dynamics.com,5558;Authentication=Active Directory Password;Database=<YourName>.com;User Id=<Mail>;Password=<Password>;
? Be sure to create an application password to avoid problems with 2 factor authentication.
https://docs.microsoft.com/en-us/azure/active-directory/user-help/multi-factor-authentication-end-user-app-passwords
Here are some possible application examples:
- Integrate data to your third-party data warehouse / BI tool
- Create complex queries which won't work in advanced search
- Use SSIS to migrate data
- Connect external reporting software
Sources:
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query
https://powerapps.microsoft.com/de-de/blog/tabular-data-stream-tds-protocol-endpoint-for-common-data-service-cds/
