Query Power Platform / Dataverse Tabular Data Stream (TDS) Endpoint using .NET and C#.

Contents

1. What is Tabular Data Stream (TDS) Endpoint?

2. What can the TDS Endpoint be used for?

   1. Reporting

   2. Batch Processing

   3. Integrations

3. Enabled TDS Endpoint for your Environment

4. Create an App Registration in Azure

5. Give App Registration Security Role in Dataverse

6. How to Build the Visual Studio Project

7. The Code

   1. Set Configuration Values

   2. Creating the SQL Connection

   3. Query Data from Power Platform

8. References

Full Source Code

Full working .NET Solution can be downloaded from the following Git repo D365Freelancing/TdSEndpointSample: Sample C# Code to query data from Power Platform TDS enpoint.

What is Tabular Data Stream (TDS) Endpoint?

Power Platform uses SQL Server as its underlying database; one of its features is the Tabular Data Stream (TDS) Endpoint, which allows developers to access and query Power Platform data using SQL directly.

For developers with a full-stack background, like myself, writing SQL queries often feels more intuitive, efficient, and powerful than using FetchXML or OData queries. It might be a preferred approach for reading Power Platform data in specific use cases.

What can the TDS Endpoint be used for?

1. Reporting - most often using Microsoft Power BI

2. Batch Processing - Heavy calculations outside of Power Platform

3. Integrations

NOTE - The TDS endpoint can be used the best "data source" for Reporitng, Batch Processing and Integrations in certian use cases. Other use cases Fabric, Synapse Data Link or Azure Aware Plug-ins are a better choice.

Note: While the TDS Endpoint is a powerful data source for reporting, batch processing, and integrations in specific scenarios, I do not recommend using it for every use case. Alternatives like Microsoft Fabric, Synapse Data Link, or Azure-aware plug-ins may be better depending on your requirements. Carefully evaluating your application's needs will help you choose the most appropriate solution.

Reporting

Reporting tools like Power BI, SQL Server Reporting Services, or Tableau often use TDS Endpoint to query data.

Batch Processing

When performing intensive calculations on Power Platform data, handling them in Azure rather than within the Power Platform itself is more efficient. This approach helps avoid API limits and/or Power Automate action constraints, ensuring smoother operations and scalability.

In my experience, the TDS Endpoint is more reliable for real-time processing than Synapse Data Link, which can encounter file-locking issues when handling real-time data. Thus, the TDS Endpoint better fits batch processing scenarios where timely data access and processing are critical.

Integrations

The TDS endpoint can be used for integrations or enriching data on integrations. I rarely use the TDS endpoint for integrations unless the integration has to be batched in real time.

Enabled TDS Endpoint for your Environment

To enable the TDS endpoint for the target environment, go to Power Platform Admin Center->Select Environment->Settings->Product->Features->TDS Endpoint.

Enabling TDS Endpoint for a Power Platform Environment

Create an App Registration in Azure

Create an application registration In Azure Portal, Azure Portal->Microsoft Entra Id-> App Registrations->New Registration

• Selected Accounts in this organizational directory only

• Empty Redirect URI

• Create a secret

Note: Make sure to copy the ClientId, TenantId, and Secret, as these will be used in the console apps' configuration.

Give App Registration Security Role in Dataverse

Go to Power Platform Admin Center->Select Environment->S2S Apps->New App User.

1. Add an App: Enter newly created app registrations client ID.

2. Business Unit: Appropriate business unit for app registration. Usually, it is the root business unit.

3. Security Roles: The appropriate security role for the app registration. When assigning a security role to an app registration, "System Administrator" is often chosen for convenience. However, to ensure a secure configuration, it is best to follow the principle of least privilege; this means creating a custom security role that grants the app registration only the minimum permissions necessary to perform its tasks. Adopting this approach minimizes potential security risks and enforces better access control.

Assign Power Platform App Registration a Security Role

How to Build the Visual Studio Project

Create a new .NET 6.0 console app project.

Add the following Nuget Packages

1. Microsoft.Identity.Client

2. Microsoft.Data.SQLClient

   
   

The Code

The complete code example is below.

Set Configuration Values

The code below contains all the configuration values required to connect to a Power Platform TDS endpoint.

1. Client ID: The client id (sometimes called application ID) of the app registration that authenticates to the TDS Endpoint. The specified app registration acts as the "user" connecting and querying data from the TDS Endpoint. The Client ID can be found on the App Registration Overview tab in Azure when creating the app registration, as per Create an App Registration in Azure section.

2. Secret: The secret of the app registration that, in combination with the client id, authenticates to the TDS Endpoint. The secret is effectively the "password". The Secret can be found on the App Registration Overview tab in Azure when creating the app registration, as per the Create an App Registration in Azure section.

3. TenantId: The Azure Microsoft Entra Tenant ID required by the MSAL library for authentication. This TennantId can be found on the App Registration Overview tab in Azure when creating the app registration, as per the Create an App Registration in Azure section.

4. ResourceId: The resource being authenticated against—in this case, the Power Platform environment. The value is the environment URL.

5. Scopes: The permissions requested by the application or client. For the TDS Endpoint, the /.default scope is used to refer generically to a resource service (API) without specifying individual permissions.

6. SQL Server: The SQL Server name for the SQL connection string. It is always the environment URL with the https:// prefix removed.

7. SQL Port: The port number configured for the TDS Endpoint, which defaults to 5558.

Creating the SQL Connection

The method BuildSQLConnectionString does two things.

1. Firstly is uses MSAL to get a Authentication Token to the TDS Endpoint

2. Secondly it creates a SQL connection utilizing Ado.NET.

MSAL is Microsoft Authentication Library and it is used to aquire authentication tokens from Microsoft Auzre Entra Id. To use MSAL Library in .NET code simply add the Microsoft.Identity.Client nuget package to the project.

Query Data from Power Platform

Finally the Ado.NET queries the Id and Name from all account records in the Power Platform environment and displays them on the console.

References

• TDS Endpoint

  • TDS 8.0 - SQL Server | Microsoft Learn

  • Use SQL to query data (Microsoft Dataverse) - Power Apps | Microsoft Learn

  • Control access of the TDS endpoint - Power Platform | Microsoft Learn

• MSAL.NET - Microsoft Authentication Library

  • Microsoft Authentication Library for .NET - Microsoft Authentication Library for .NET | Microsoft Learn

  • Initialize MSAL.NET client applications - Microsoft Authentication Library for .NET | Microsoft Learn

  • Scopes and permissions in the Microsoft identity platform - Microsoft identity platform | Microsoft Learn

• ADO.NET

  • SqlDataReader Class (System.Data.SqlClient) | Microsoft Learn

In Summary

This blog post has walked you through how to