Connect to Common Data Service using SQL Server Management Studio - Dynamics 365

One of the announcements from Microsoft is ability to write a T-SQL query against a Common Data Service Database. This also includes ability to connect to Common Data Service using direct query in Power BI, SQL. Connection as of now provides you read-only access to entity data. This allows you to execute SQL queries against the entity data table. Table columns provide attribute data of an entity. No custom views of data have been provided.

Note: 

• This is a preview feature from Microsoft and may change in the future.
• This feature is not available in all regions.

Security and Authentication

The Common Data service endpoint SQL connection used a Common data service security model for data access. Data can be obtained for all entities which user has access to in CDS. Only Azure AD authentication is supported.

Why this is a nice feature?

Previously when dealing with data in Common Data Service, we had few options to retrieve data.

• FetchXML
• Exporting CDS data to SQL Database using Data Export Service or alternate methods 
• Use Power Platform connectors

With this new feature, we can connect directly to Common Data Service database and write T-SQL queries. Currently, we are restricted to read-only access.

In my case, I am using Version  9.1.0000.19251 online:

Let's look at how to enable this preview of this feature and how to use it.

Lets now enable TDS in Power Platform Admin Center, TDS is Tabular data stram protocol used to transfer data between client and database servers.

To enable this in Power Platform, log into Power Platform Admin Center, and select your environment.

Click on Settings -> Products -> Features

Next is to Enable TDS endpoint and save the settings.

SQL Server Management Studio

Ensure you have the latest version of SQL Server Management Studio.

• Server Type: Database Engine
• Servername: yourdynamicsorg, 5558 (ex: crm34343.crmynamics.com, 5558)
• Authentication: Azure Active Directory
• Username: Azure User Name
• Password: Azure Ad Password

Query Accounts to get top 1000 rows

Supported operations and data types

The list of supported SQL operations inclused:

1. Batch Operations
2. Select
3. Aggregation function (i.e Count and Max())
4. Unions and Joins
5. Filtering

In case you did not enable TDS in Power Platform, you will see error "TDS endpoint is disabled for this organization".