Use SQL Connection to Query CDS / Dynamics 365 Data (Works with SSMS and Power BI)

Hello Learners, here is the key take-away from Microsoft Business Applications Summit (MBAS)-2020.

Power Platform provides Tabular Data Stream (TDS) endpoint for CDS, using which we can access environment’s data using SSMS just like how we access the data in on-premise environment (*Only Read is permitted, cannot perform Updates)

**Image Source – Microsoft Docs


Below are the key points to remember.

  1. Its in Preview and not available for all regions. It works only with version 9.1.0.17437 and above. Check if the latest version is available for your region here. Alternatively you can check the version of your Environment from Settings –>About.

2. To use the SQL connection, you need to enable the Tabular Data Stream (TDS) endpoint using below options

a) Using OrgDBOrgSettingsToolStep-By-Step guidance for the same is available here.
b) Go to https://admin.powerplatform.microsoft.com -> Environments ->(select environment)-> Settings -> Features , and turn Enable TDS EndPoint on.



3. SQL Connection’s Server Name would be <organization url>,5558 (5558 is the port used for TDS endpoint. it is same for all organizations). For authentication, we can use the same Active Directory Credentials that we use to access the Organization. Application Users with Azure Client ID are not supported for now. Step-By-Step guidance is available here

4. The same SQL Connection Can be used in Power BI to analyse data. Step-By-Step guidance HERE

References for more details:

1) https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query
2) https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi
3) https://mymbas.microsoft.com/sessions/5b645f84-8689-4545-9b8a-af94fa2fad7c

Author: Sudheer Reddy Nelakurthi

Power Platform developer for 8 years

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