Among the latest features released by Microsoft, we have the possibility (finally!) of being able to connect in the cloud to the Common Data Service (CDS) database to perform our queries. The SQL connection provides read-only access to the entity data of the target CDS environment. Below I show you all the necessary steps.
First of all, make sure you have the SSMS updated version at least 18.4:
Next you need to enable the TDS endpoint functionality. 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
- Username: [your username]
- Password: [your 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 (WOW!):
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.
Hope it helps and happy 365Power’ing!