This post will look at how to configure and use the TDS endpoint with Common Data Service or Dynamics 365 Model Apps. At Microsoft Business Applications Summit 2020, Microsoft released a preview feature that exposes a read-only endpoint that can be used with Microsoft PowerBI. You can also use it for querying the data using SQL Server Management Studio v18.4.
Historical Context of Tabular Data Stream
Before we go into the details, time for a quick history lesson! Back in the CRM 4.0 days, Microsoft added Filtered Views in the MSCRM SQL database. These Filtered Views apply the security model (security roles and permissions) to the user querying the data. It also returns the option set label and value as well as lookup IDs and names.
Fast forward to today, these Filtered Views are still in place and used within Dynamics 365/Common Data Service whenever an authenticated user opens an entity list, uses Advanced Find or uses the SDK to retrieve data. An important thing to consider here is that any plug-in or business layer logic is not applied. Only security roles permissions apply at the database level when querying data.
Walkthrough – Using Tabular Data Stream (TDS) with Common Data Service
Since this is a preview feature, we must enable it. Following are the few requirements that you need to fulfill to enable the feature:
- Your Common Data Service environment should be equal to or greater than version 9.1.0.17437. You can check this from the Admin Portal or from within the Advanced Settings About page.
- Download and extract the OrgDBSettingsTool from https://www.microsoft.com/en-us/download/details.aspx?id=56131
- Make the following updates to the Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config file.
Setting Name: OrgDBOrgSettingsTool_CrmDiscoveryService_CrmDiscoveryService
Setting Value: https://disco. <!– Add your regions Discovery Service Endpoint Address.–>
Setting Name: OrgDBOrgSettingsTool_SKU
Setting Value: Online
Setting Name: OrgDBOrgSettingsTool_UserName
Setting Value: <!– admin user login–>
Setting Name: OrgDBOrgSettingsTool_OrgServiceUri
Setting Value: https://<!– Add your environment’s Org Service Endpoint Address. –>
4. Add the following node within the configuration node
<runtime>
<AppContextSwitchOverrides value=”Switch.System.Net.DontEnableSchUseStrongCrypto=false”/>
</runtime>
Finally, run the following command at the command prompt in the same directory where you extracted the tools. Make sure to replace <org-unique-name> with the organization’s unique name from the Developer Resources page in Dynamics 365 Customizations.
Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u <org-unique-name> EnableTDSEndpoint true
After updating the endpoint successfully, you can now open SQL Server Management Studio v18.4 and connect.
Server Name: alphabolddev22.crm3.dynamics.com,5558 (note the port 5558)
Authentication: Azure Active Directory – Password
Username: <your username>
Password: <your password>
A couple of things to note here, the Audit table is not available, and the querying attachment table will not return data. If you try to run a create or update SQL script, it will not execute because of the read-only connection.
Finally, let’s try to run a SQL query against the Filtered Views in the database.
Conclusion
The TDS endpoint for Common Data Service opens many possibilities, not only limited to SQL Server Management Studio v18.4 but also with PowerBI and SSRS or paginated reports. In some scenarios, it can also eliminate the need for using Data Export Service as the data exported by Data Export Service is not secured by any security roles or permissions.
I hope that you found this blog post helpful. Please do give it a try and share your thoughts with us in the comments below!
Happy Configuring!
If you have any question or queries, do not hesitate to reach out to us!
References:
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query
https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi
