Use SQL to query dataverse - PowerApps

Use SQL to query data

[This topic is pre-release documentation and is subject to change. Note that only the SQL data connection is in preview. Power BI is General Availability (GA)]

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.

Applications support

TDS (SQL) endpoint applications support for Power BI and SQL Server Management Studio is described below.

SQL Server Management Studio (Preview)

 Note

A compatibility issue has been found with the SQL Server Management Studio 18.9.2 build. A fix is being investigated. Until the fix is available please use build 18.9.1 of SQL Server Management Studio. This note will be updated once a fix is available.

You can also use SQL Server Management Studio (SSMS) version 18.4 or later with the Dataverse endpoint SQL connection. Examples of using SSMS with the SQL data connection are provided below.

Security and authentication

Only Azure Active Directory authentication is supported. SQL authentication and Windows authentication aren't supported. Below is an example of how to logon to the SQL connection in SSMS. Notice the server name is the organization address URL.

 Note

Ports 1433 and/or 5558 need to be enabled to use the TDS endpoint from a client application such as SSMS. If you only enable port 5558, the user must append that port number to the server name in the Connect to Server dialog of SSMS - for example: myorgname.crm.dynamics.com;5558.

Example table data queries

Below are a couple of example queries composed in SSMS. The first image shows a simple query using aliases and result ordering.

Transact-SQLCopy

 

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

This next query shows a JOIN.

Transact-SQLCopy

 

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

Power BI

You can use the Analyze in Power BI option (Data > Tables > Analyze in Power BI) in Power Apps (https://make.powerapps.com) to use the Dataverse connector to analyze data in Power BI Desktop. More information: View table data in Power BI Desktop

 Note

To enable this feature, see the TDS endpoint setting in Manage feature settings. Once enabled you should see a button Analyze in Power BI in the command bar of Power Apps.

Supported operations and data types

Any operation that attempts to modify data (that is, INSERT, UPDATE) will not work with this read-only SQL data connection. For a detailed list of supported SQL operations on the Dataverse endpoint, see How Dataverse SQL differs from Transact-SQL.

The following Dataverse datatypes are not supported with the SQL connection: binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp, choices.

 Tip

partylist attributes can instead be queried by joining to the activityparty table as shown below.

Transact-SQLCopy

 

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

Lookup column type behaviors

Dataverse lookup columns are represented as <lookup>id and <lookup>name in a result set.

Choice column type behaviors

Dataverse choice columns are represented as <choice>Name and <choice>Label in a result set.

 Tip

After making changes to labels for a choice column, the table needs to have customizations published.

Limitations

There is an 80-MB maximum size limit for query results returned from the Dataverse endpoint. Consider using data integration tools such as Azure Synapse Link for Dataverse and dataflows for large data queries that return over 80 MB of data. More information: Importing and exporting data

 Tip

To help keep the size of the returned data within acceptable limits, use as few multi-line text columns and choice columns as possible.

Dates returned in query results are formatted as Universal Time Coordinated (UTC). Previously, dates were returned in local time.

Querying data using SQL does not trigger any plug-ins registered on the RetrieveMultipleRequest or RetrieveRequest messages. Any rewriting of the query or results that would normally be performed by such a plug-in will therefore not take effect for a SQL query.

Queries using the TDS endpoint execute under the service protection API limits.

Troubleshooting connection problems

Below are some know error conditions and how to resolve them.

 Note

Ports 1433 and/or 5558 need to be enabled to use the TDS endpoint from a client application such as SSMS. If you only enable port 5558, the user must append that port number to the server name in the Connect to Server dialog of SSMS - for example: myorgname.crm.dynamics.com;5558.

Authentication

Only Azure Active Directory authentication is supported on the Dataverse endpoint SQL connection. The preferred authentication mechanism is "Azure Active Directory – Universal" with multi-factor authentication (MFA). However, "Azure Active Directory – Password" will work if MFA is not configured. If you try to use other forms of authentication, you will see errors like the following.

• Error returned when using Azure Active Directory – Integrated authentication.

“Login failed: The HTTP request was forbidden with client authentication scheme 'Anonymous'. RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)”

• Error returned when using SQL Server authentication.

“Login failed: Request is not authenticated. RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient Data Provider)”

• Error returned when using Windows authentication.

“Login failed: Request is not authenticated. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient Data Provider)”

Blocked ports

A blocked port error may look something like the following.

The solution is to verify the TCP ports 1433 or 5558 from the client are unblocked. Use one of the following methods to do that is described below.

Use PowerShell to validate connection with TDS endpoint

1. Open a PowerShell command window.
2. Run the Test-connection command. 
   Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

If the connection is successful a line "TcpTestSucceeded : True" will be returned.

Establish a telnet session to the TDS endpoint

1. On a Microsoft Windows computer, install/enable telnet.
   1. Choose Start.
   2. Select Control Panel.
   3. Choose Programs and features.
   4. Select Turn Windows features on or off.
   5. Choose the Telnet Client option.
   6. Select OK. A dialog box appears to confirm the installation. The telnet command should now be available.
2. Run a telnet command in a Command window.
   telnet <environmentname>.crm.dynamics.com 1433

If the connection is successful, you will be in an active telnet session. If unsuccessful, you will receive the error:

“Connecting to <environmentname>.crm.dynamics.com… Could not open connection to the host, on port 1433: connect failed”.

This means the port has been blocked at the client.