Among the latest features released by Microsoft, we have the possibility (finally!) of being able toconnect in the cloud to the Common Data Service (CDS) database to perform our queries. The SQL connection providesread-onlyaccess to the entity data of the target CDS environment. Below I show you all the necessary steps.
First of all, make sure you have theSSMS updated version at least 18.4:
Next you need to enable theTDS endpointfunctionality. These settings can be found in the Power Platform admin center by going to: Environments >[Your Environment Name] > Settings > Product > Features>TDS endpoint:
Well, now you have all the requirements to be able to connect. Now, open SSMS and enter the following information to perform the connection:
Server Type:Database Engine
Server Name:[your organization name],5558
Auth:AAD – Password
Notice that the Server Name is the organization address URL followed by a comma and the port value of 5558:
After establishing the connection, you will be able to see all the tables of the CDS:
The list of supported SQL operations includes:
Batch operations (such as Backup, Synchronization, etc…)
Aggregation functions (i.e., Count() and Max() functions)
UNIONs and JOINs
IMPORTANT!the Common Data Service endpoint SQL connection uses the Common Data Service security model for data access and is read-only. Data can be obtained for all entities to which a user has access to in Common Data Service.