cancel
Showing results for 
Search instead for 
Did you mean: 

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:

 

1

 

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:

 

3

 

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:

 

2

 

After establishing the connection, you will be able to see all the tables of the CDS:

 

4

 

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.

 

5

 

Hope it helps and happy 365Power’ing!

Comments

It's not working for my environment (community plan) 😣

Is it a known limitation? @capuanodanilo 

Is there a limitation on the amount of joins that can be done in a query?
@capuanodanilo 

@DanielDamico the feature isn’t active on all regions. What is your?

@Avigail I don't think there are any particular limitations, but being a new feature is to try. Maybe let us know!

Hi @capuanodanilo , my region is South America.

2020 release wave 1 already "on".

And w/ the "Enable TDS endpoint" toggle also "on"

 

Thanks

EnvironmentDetails(PowerPlataformAdminCenter)_LI.jpg

I saw that there was an email sent out earlier regarding a Bug with access, leading to Microsoft disabling the feature. Anyone else received that or any details on expected time to fix?

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Systems developer
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim