SQL query to Dynamics 365 online
In this blog, we will dive into how we can use our local sql server managment studio (18.6 or later) to connect to common data service endpoints to query records. The connected SQL database is read-only but this will provide us with a lot of flexibility to visualize our data in SQL the way we want. This is the most demanding feature were missing in the past as we use it on-premises dynamics servers for report building and other scenarios. Well, note, this is a preview feature and not recommended for production environments just recently enabled in all Azure regions. You can read about this here.
This feature enables you to use Power BI desktop to view tables in Common Data Service. The table row data that you can access from your environment is read-only. Data access uses the Common Data Service security model that is the same used to access table row data using a Power Apps app. More information here.
Prerequisite
To utilize this feature we need to enable Tabular Data Stream (TDS) endpoint for Common Data Service in an environment.
- Select your environment in the power platform admin center and make sure the environment has at least version 9.1.0.17437.
- Now navigate to: Settings -> Features
- SQL Server Management Studio (SSMS) version 18.4 or later.
How to Connect to CDS
Open SSMS and click connect:
-
Server Name: {Orgurl},{5558}
-
Authentication: Azure Active Directory - Password (only supported for now)
-
CRM username and password
Example entity data queries
select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc
Supported operations and data types
The list of supported SQL operations includes:
- Batch operations
- SELECT
- Aggregation functions (i.e., Count() and Max() functions)
- UNIONs and JOINs
- Filtering
Any DML operations like (i.e., INSERT, UPDATE) will not work as this is a read-only SQL data connection. Common Data Service option sets are represented as <OptionSet>Name and <OptionSet>Label in a result set.
The following Common Data Service datatypes are not supported with the SQL connection: binary, image, text, sql_variant, varbinary, virtual, HierarchyId, managed property, file, xml, party list, timestamp.
Partylist attributes can instead be queried by joining the activity party table as shown below.
Plug-ins
Querying data using SQL does not trigger any plug-ins registered on the RetrieveMultipleRequest or RetrieveRequest messages. Any re-writing of the query or results that would normally be performed by such a plug-in will therefore not take effect for a SQL query.
Analyze in Power BI
You can also use the Analyze in Power BI option (Data > Entities > Analyze in Power BI) in Power Apps (https://make.powerapps.com) to use the SQL connection feature to analyze data in Power BI Desktop.
If you do not see this button, your environment does not yet have the feature. More information can be read here.
I hope the CRM community will like this feature and can utilize it to their needs. I also like this feature because unlike the Data Export service you can directly query the live CRM database for records, but be careful and do not run heavy bulk queries on the database as this will impact the performance of the main database.
Take care and stay safe! 😊