How To Query Dataverse In Microsoft SQL Server Management Studio (SSMS)?

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.

SNo Field Name Field Value
1 Server Type Select Database Engine
2 Server Name Provide CRM Org URL, append comma (,) and the Port Number (5558). Example:arunpotti.crm.dynamics.com,5558
3 Authentication Azure Active Directory – Password
4 User name Provide your Dynamics 365 Login User Id
5 Password Provide your Dynamics 365 Login Password

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

Step 3

SQL Server Management Studio connected to your Dataverse environment.

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

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.

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

Step 5

Click on your Database and Click on New Query.

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

Step 6

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

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

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.