Use SQL to Query CDS (Common Data Service) Data

Since I attended the MBAS 2020 (Microsoft Business Applications Summit), wanted to try this feature and took lot of time to achieve this, hence thought of sharing this blog with you all to explain the errors I faced and how to addressed it. Finally I could query the CDS data using SQL Query.

What a relief, now we can’t say that we cannot run SQL query on Dynamics 365/CDS data.

Let’s look at the steps as how to enable this feature & use it.

Step 1: Make sure you have SQL Server Management Studio (SSMS) version 18.4 or later with the Common Data Service endpoint SQL connection.

Step 2: Connect the CDS using the details mentioned in below image.

At this stage I faced an error as shown below which took while for me to solve it.

To address this issue, we need to enable one setting on CDS organization called “EnableTDSEndpoint” for which I was following steps mentioned here (https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi) for almost 2 days but it didn’t work.

Finally, I found one managed solution on GitHub which helped achieving this (https://github.com/seanmcne/OrgDbOrgSettings). Thank you Sean McNellis for this solution.

Let’s look at the steps to enable this setting

Step 3: Import this managed solution in your organization for which you want to enable this setting.

Step 4: Upon successful import of this solution, open this solution & search for “EnableTDSEndpoint” and click on Add link as highlighted below

Note: You should have system admin role for your organization to do this step

Step 5: Once click on Add, this setting will get added in your organization with default value as “false”, now click on Edit link to change this to true.

Step 6: Once you click on Edit link, it will show a pop-up like this in which you need to enter the true as value & click on Update button.

Step 7: Once you finish above activity, you should see the true value in current value column for “EnableTDSEndpoint” setting.

Step 8: Now you should be able to login to the SQL Management as per Step 2 and be able to see the Organization in read-only mode. Now you can query your Dynamics 365/CDS data just like any other databases.

The list of supported SQL operations includes:

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

Note: Any operation that attempts to modify data (i.e., INSERT, UPDATE) will not work as this is a read-only SQL data connection. Kindly refer this URL for more details https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

Infusai is Microsoft’s preferred Dynamics 365 Partner serving out of India, Singapore, Europe, USA & Canada.

Click here for any help/support required