Using Tabular Data Stream (TDS) with Common Data Service (Dynamics 365)

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: 

  1. 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. 
  2. Download and extract the OrgDBSettingsTool from https://www.microsoft.com/en-us/download/details.aspx?id=56131 
  3. 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.

In 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!

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 

Leave a Reply

Your email address will not be published. Required fields are marked *