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
- Aggregation functions (i.e., Count() and Max() functions)
- UNIONs and JOINs
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
I installed the managed solution but there’s no EnableTDSEndpoint row?
Can you check & confirm your environment version, it should be 18.104.22.16837 or above
Ah – yes it is 22.214.171.12454 so not quite up there. How can I upgrade it?
you can check your power platform admin center (https://admin.powerplatform.microsoft.com/environments) to see where update is available?
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)
Ignore me. I thought the port 558 was with a full stop and not a comma. It is working now.
Glad that it worked for you, Thanks
Does anyone know when this is going to be released out of preview? Have they provided any updates, I sure can’t find any