Use SQL to Query CDS (Common Data Service) Data

By | May 15, 2020

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.

Note: Make sure you add 5558 port after your Org URL

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

7 thoughts on “Use SQL to Query CDS (Common Data Service) Data

  1. Lewis Harris-Manley

    I installed the managed solution but there’s no EnableTDSEndpoint row?

    Reply
    1. Dani Manoj Post author

      Can you check & confirm your environment version, it should be 9.1.0.17437 or above

      Reply
      1. Lewis Harris-Manley

        Ah – yes it is 9.1.0.17154 so not quite up there. How can I upgrade it?

        Reply
  2. Dean Terry

    Hi Dani,

    Great article. I have set the “EnableTDSEndpoint” flag but I’m getting a different error. See below. Any ideas?

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    Reply
    1. Dean

      Ignore me. I thought the port 558 was with a full stop and not a comma. It is working now.

      Reply

Leave a Reply