[UPDATE - June 19, 2024] We have updated this blog post to use the recently released Premium SQL Server components that were made available in our SSIS Productivity Pack v24.1 release. The newly introduced Premium SQL Server features enable connection to modern SQL Server authentications which include Dynamics 365 TDS endpoints. [/UPDATE - June 19, 2024]
FetchXML is a proprietary query language that is used to retrieve data from a CDS/CRM instance. However, FetchXML query is not as flexible nor extensive as an SQL query and has its own restrictions, which we discussed in a previous blog post (Limitations with CRM FetchXML). Considering these limitations, most users prefer to read CDS/CRM data by connecting to the CDS/CRM database directly, where they can build the query with their most familiar query language – SQL query, and easily fetch data with complex joins. But when it comes to CRM Online and CDS environments, the database accessibility is no longer available requiring users to either use FetchXML query or build the OData query through a WebAPI connection to be able to work with the CDS/CRM Online data. Microsoft has introduced the read-only SQL data connection for CDS/CRM Online. Further details about this feature can be found on the corresponding Microsoft documentation page.
In this blog post, we will demonstrate how to connect to the CDS/CRM Online application through the SQL Server Connection Manager and query data using the Premium SQL Server Source component, a part of our SSIS Productivity Pack.
Enabling TDS Endpoint Feature
To enable the TDS endpoint, you must sign in to the Power Platform Admin center, then head to Environments > [select an environment] > Settings > Product > Features.
Make sure you have the System Administrator or System Customizer security role or equivalent permissions to enable the TDS endpoint.
Configuring the SQL Server Connection Manager
The SQL data connection becomes available once the TDS endpoint is enabled. You can now configure the SQL Server Connection Manager offered in our Productivity Pack to establish the database connection to the CDS/CRM Online instance.
SQL Server Connection Manager is offered in our 2024 Wave 1 release, it offers support of 10 authentication modes:
- SQL Server Authentication
- Windows Integrated Authentication
- OAuth - Authorization Code
- OAuth - Client Credentials
- OAuth - Client Credentials Certificate
- Azure Active Directory - Password
- Azure Active Directory - Universal with MFA
- Azure Active Directory - Service Principal
- Azure Active Directory - Managed Identity
- Azure Active Directory - Default
The screenshot below shows an example of the SQL Server Connection Manager configuration. The server name is the organization address URL followed by a comma and the port value of 5558.
Note the database discovery is not supported at this moment, so you need to manually specify the database name, which is the CDS/CRM organization name in this case.
Querying Data Using Premium SQL Server Source Component
Once the Server SQL Connection Manager is configured, you can now query CDS/CRM data easily using Premium SQL Server Source component. With the use of SQL query, you can retrieve data from your Dynamics application with ease, which can be more efficient than using FetchXML query, and it is more intuitive to work with Dynamics application data as the source of your integration.
Closing Note
When faced with limitations of FetchXML, developers can query CDS/CRM Online data using SQL query instead, thus providing much better performance and more flexibility to perform filtering and join entity actions.