How to query Dataverse in Microsoft SQL Server Management Studio (SSMS)?

A SQL data connection is available on the Microsoft Dataverse endpoint. The SQL connection provides read-only access to the table data of the target Dataverse environment thereby allowing you to execute SQL queries against the Dataverse data tables. No custom views of the data have been provided. The Dataverse endpoint SQL connection uses the Dataverse security model for data access. Data can be obtained for all Dataverse tables to which a user has access to.

In this article, will explain how to connect to the Dataverse in SSMS.

Prerequisites:

1. Enable TDS on your Dynamics CRM Environment.
   
2. Install SQL Server Management Studio.

Note: Use SQL Server Management Studio (SSMS) version 18.4 or later to connect to the Dataverse. 

Follow the below steps for connecting to the Dataverse.

Step 1: Open Microsoft SQL Server Management Studio (SSMS).

Step 2: Provide all the required details.

Step 3: SQL Server Management Studio connected to your Dataverse environment.

Step 4: Expand the Databases folder to see your Database with your environment name.

Expand your environment database to Expand Tables to see all your entities in Read-Only mode.

Step 5: Click on your Database and Click on New Query.

Step 6: Write your query and Click on Execute to see the result.

Limitations:

1. There is an 80-MB maximum size limit for query results returned from the Dataverse endpoint.
   
2. Dates returned in query results are formatted as Universal Time Coordinated (UTC). Previously, dates were returned in local time.
   
3. Querying data using SQL does not trigger any plug-ins registered on the RetrieveMultipleRequest or RetrieveRequest messages.
   
4. Queries using the TDS endpoint execute under the service protection API limits.

Hope you have successfully connected to the Dataverse environment and executed your queries in SQL Server.

Please like and share your valuable feedback on this article.