Use SQL Server Management Studio (SSMS) to query data from Common Data Service (CDS)

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…)
  • SELECT
  • Aggregation functions (i.e., Count() and Max() functions)
  • UNIONs and JOINs
  • Filtering

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.