Rajeev Pentyala – Technical Blog on Power Platform, Azure and AI

Did you know that you can connect to your Dataverse environment using SQL Server Management Studio (SSMS) and query data with SQL?

Use Case:

Let’s say you’re migrating around 2 million ‘Account’ records into your Dataverse environment using SSIS. While your SSIS package is running, you may want to track the count of Accounts and view the data in real time.

One option is to connect to your Dataverse environment using SQL Server Management Studio (SSMS).

Prerequisites

• The Enable TDS endpoint setting must be enabled in your environment. That setting is enabled by default. More information: Manage feature settings

• SQL Server Management Studio (SSMS) version 18.12.1 or later

Once you have the prerequisites, let’s go through the steps to connect.

Connect to Dataverse environment using SSMS:

• To connect to Dataverse from SSMS, only Microsoft Entra ID authentication is supported.
• Open SQL Server Management Studio (SSMS) and click Connect to Server.
• Provide the following details:
  • Server name : Dataverse organization URL
  • User name : Your User Id
  • Password : Your password

• Click on Connect
• You’ll now be connected through a read-only SQL data connection. Any operation that attempts to modify data (such as INSERT or UPDATE) is not supported.
• You can, however, run queries. For example, the query below retrieves the count of records in the Account table.

Important Points:

• Lookup columns are represented as <lookup>id and <lookup>name in a result set.
• Choice columns are represented as <choice>Name and <choice>Label in a result set.
• The following Dataverse datatypes aren’t supported with the SQL connection: binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp, choices.
• In addition, tables types ‘virtual’ and ‘audit’ aren’t supported

Refer Use SQL to query Dataverse data for more details.

🙂