Connect to D365 TDS (SQL) Endpoint from .Net Core 3.1

Recently i needed to implement a timer based Azure function to query and process data in D365 CE tables. Due to increasing volume of data i needed apply filters while reading data i.e, either Fetch / Query Expression / WebApi or somehow make it work with SQL. Here TDS (Table Data Stream Protocol: read here) came in quite helpful as I could do much extended filtering in SQL than querying through Fetch or WebApi queries, saving all in-memory filtering complexity. So the next step was to connect to TDS endpoint for .Net Core 3.1 function code, as classic ADO.Net / SQL Linq DataExtensions is not supported.

Cutting the long story short, I needed to connect to TDS (SQL) endpoint for my D365 CE Organization (Environment) database from my .Net Core 3.1 Function code (C#), without ADO.Net. Reading a few different community sites / posts I found the way to work it out. The solution itself is quite easy, as long as one understands mechanics of OAuth 2.0 authentication (read more here) using Service Prinicpal (aka Azure App Registration).
Read more here, In order to understand the need and how to create SP (aka Azure App Registration).

Note: TDS endpoint can be enabled for specific environment (org) using Power Plaftform Admin portal (admin.powerplatform.microsoft.com). For specific Environment, browse to Settings -> Product -> Features -> TDS Endpoint. Try connectivity using SQL Server Management Studio, as also mentioned here in MS Docs.

These Nugets packages are in use by my .Net Core 3.1 App

Following code can be used to generate OAuth 2.0 AccessToken to be used later in making connection, where:
{tenantid} is the tenant’s id of the D365 environment – can be found from either Azure Portal or Power Apps Maker site (Session Details)
resourceId is the url D365 environment: https://<org&gt;.crm4.dynamics.com
clientId is from Azure App Registration
clientSecret is from Azure App Registration

var ctx = new AuthenticationContext($"https://login.microsoftonline.com/{tenantId}");

var authenticationResult = ctx.AcquireTokenAsync(resourceId, new ClientCredential(clientId, clientSecret))
                          .ConfigureAwait(false)
                          .GetAwaiter()
                          .GetResult();
//Tip: cache authenticationResult based on authenticationResult.ExpiresOn and regenerate based on current time.
     

Following code shows how to use AccessToken from OAuth 2.0 AccessToken, where:
connectionString is built based on D365 org / environment:
Server=<org>.crm4.dynamics.com

using (var d365DbContext = new DbContext(connectionString))
{
       ((System.Data.SqlClient.SqlConnection)d365DbContext.Database.Connection).AccessToken = authenticationResult.AccessToken;
	d365DbContext.Database.SqlQuery<object>("select count(1) from contact").FirstOrDefault();
}
     

Note: As of 15-Dec-2021 MS Doc (read here) it is possible to connect to TDS endpoing using Microsoft.Data.SqlClient 4.0.0 (SqlClient Github) as in code below. However EntityFramework 6.4.4 (for using with DbContext) does not, as yet, come with a provider that uses Microsoft.Data.SqlClient. Due to this, one either has to work without EF or use/build a custom provider. One such is available here, using EntityFramework Nuget package from Erik while using Microsoft.Data.SqlClient 2.1.3

using (var connection = new Microsoft.Data.SqlClient.SqlConnection(@"Server=<org>.crm4.dynamics.com;Authentication=Active Directory Service Principal;User Id=clientId;Password=clientSecret"))
{
      var command = new Microsoft.Data.SqlClient.SqlCommand("select count(1) from contact", connection);
      connection.Open();
      var reader = command.ExecuteReader();
}
                      

Hope this helps!