You can connect to Dynamics 365 Cloud via SQL Server Management Studio — Here’s how! | R.iT News-Blog

04.08.2020 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.

Available Operations

The following operations are available right now:

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

How it is done

To try it out by yourself, you must activate the TDS preview:

1. Browse admin.powerplatform.microsoft.com
2. Select your instance
3. Go to preferences
4. Open Product ? Features
5. 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

Examples

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/

Originally published at https://www.rit.de.