Connecting Dynamics 365 CDS database using SQL & SQL Server Management Studio (SSMS)

Microsoft has released a long awaited preview feature to enable Tabular Data Stream (TDS) endpoint (a SQL data connection) for Common Data Service in an environment, which helps to use SQL and SQL Server Management Studio (SSMS) to query CDS database/CRM organization database. 

Prerequisites To use this feature 

• Dynamics environments version should be 9.1.017437 or higher 
• SQL Sever Management Studio (SSMS) version should be 18.4 or higher
• System Administrator rights to manage environment features in Power Platform Admin center (admin.powerplatform.microsoft.com)

To query CDS entity data from the back end tables of database, follow below steps.

1. Enable TDS endpoint feature 

• Login to Power Platform Admin Center   https://admin.powerplatform.microsoft.com/   
• Go to Environments > [select an environment] > Settings > Product > Features > Enable TDS endpoint.    
• Turn ON Enable TDS endpoint as shown below

         

           

2. SQL Server Management Studio - Connect to SQL Server

• Open SQL Sever Management Studio (version 18.4 or later)
• Click on connect to SQL Server
• Enter SQL Connection details and click Connect
• Server Name : Organization Name + ",5558"
• Authentication : Azure Active Directory - Password
• User Name: cds/crm system user id
• Password: user password

  

3. SQL Query & Explore data

• After connecting successfully you can see the database objects in Object Explorer
• You can open the New Query and query entity tables 😊

• 
  
  
• 

4. Important to Note 

• This feature only provides read only access to entity tables.
• Environment related tables may not have access to query.
• No ability to create new tables, views, procedure, constraints, indexes etc. yet due to security reasons.