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.

SNoField NameField Value
1Server TypeSelect Database Engine
2Server NameProvide CRM Org URL, append comma (,) and the Port Number (5558). Example: arunpotti.crm.dynamics.com,5558
3AuthenticationAzure Active Directory – Password
4User nameProvide your Dynamics 365 Login User Id
5PasswordProvide your Dynamics 365 Login Password

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.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.