In an unexpected, but very welcome announcement, Microsoft have revealed the ability to query the Common Data Service using T-SQL.
Microsoft have traditionally prevented database access to their hosted CRM products, so for analysts familiar with using SQL queries, they have had to find alternative methods of working with the underlying data, such as with data exports and Power BI. This new functionality, which is currently in preview in a number of regions, allows for familiar SELECT and JOIN commands to be used, amongst others.
How do I know if I have access to this new functionality?
If you visit https://make.powerapps.com/, under Data>Entities, if you see an ‘Analyse In Power BI’ option as below:
then it means the new functionality has been enabled for your organisation. When this endpoint is used within Power BI, not only is the existing CDS security model preserved, but real-time queries are supported, meaning that data refreshes are not required.
Following this, a one-time update to the organisation you wish to connect to needs to be carried out. Microsoft have provided a step-by-step guide to the process here. (Note: at the time of writing this post, there is an error in one of the steps – please refer to the Feedback at the article for the correction).
UPDATE – Microsoft have now added a switch in the Power Platform Admin Center that toggles this functionality on and off
How do I connect using SQL Server Management Studio?
Refer to the following example of the required connection parameters.
The key part to notice is adding the “,5558” to the end of your CDS environment address. Currently, only Azure Active Directory authentication is supported and the connection uses the same security as within the Common Data Service itself, so if you have access to an entity in the CDS, you’ll see it in your SQL connection.
What are the commands supported?
- Batch operations
- SELECT
- Aggregation functions, i.e. Count() and Max() functions
- UNIONs and JOINs
- Record Filtering
Can I use this SQL connection to update records?
No. This connection is purely intended for read-only access. Any attempts to insert or update records will fail.
Summary
It’s good to see Microsoft opening up the Common Data Service to make it even more flexible than is was already. The robust, straightforward nature of the data structure highlight the strengths of the Power Platform and we look forward to future enhancements in due course.
