Dataverse SQL – Azure Function

Dataverse provides a TDS, Tabular Data Stream, endpoint that allows you to query your data using SQL. This feature is especially useful for handling complex data queries or querying large volumes of data. Keep in mind that Dataverse SQL capabilities do not provide direct access to the underlying SQL database; instead, they emulate a SQL Server endpoint. As a result the endpoint only supports read-only operations and updates are not allowed.

A challenge in some organizations is that SQL traffic is blocked on desktops so you are prevented from running SQL queries against Dataverse. One option to get around this is to use SQL 4 CDS which is a plugin for XRM Tool Box created by Mark Carrington. This allows you to run SQL queries against your Dataverse environment and because it converts your SQL into APIs calls it does not generate SQL traffic. SQL 4 CDS also allows you update records in Dataverse using SQL which the Dataverse TDS endpoint does not support. Unfortunately it can be slow when you have large volumes of data.

Another options is to create an Azure Function to run queries against your Dataverse SQL endpoint as follows

• Create an Azure Function that accepts a string parameter containing the SQL query
• Use a service principal to connect to the Dataverse SQL endpoint and run the query.
• Format the results as Json before returning the results to the caller.

[FunctionName("DataverseSQL")]
public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
    ILogger log)
{
    string query = req.Query["query"];
    string token = await GetToken();
    string database = Environment.GetEnvironmentVariable("Url").Replace("https://", "").TrimEnd('/'); ;
    string connectionString = $"Server={database}; Encrypt=True";

        using (SqlConnection srcConn = new SqlConnection(connectionString))
        {
            srcConn.AccessToken = token;
            await srcConn.OpenAsync();

            DataTable dataTable = new DataTable();
            using (SqlCommand srcCmd = new SqlCommand(query, srcConn))
            {
                using (DbDataReader reader = await srcCmd.ExecuteReaderAsync())
                {
                    dataTable.Load(reader);
                }
            }

            string resultString = ConvertDataTableToJson(dataTable);
            return new OkObjectResult(resultString);
        }

}

Usually when connecting to Azure SQL using a service principal you create a connection string containing the service principal credentials. This approach does not work with Dataverse. Instead, you must generate an access token and include it in the connection string. As shown in the code below, the credentials and the Dataverse URL you are requesting access to are passed to https://login.microsoftonline.com/ to obtain a token, which is then used in the connection string.

 static async Task<string> GetToken()
 {
     string clientSecret = Environment.GetEnvironmentVariable("Secret");
     string tenantId = Environment.GetEnvironmentVariable("TenantId");
     string clientId = Environment.GetEnvironmentVariable("ClientId");
     string sqlDatabaseUrl = Environment.GetEnvironmentVariable("Url");

     var confidentialClient = ConfidentialClientApplicationBuilder.Create(clientId)
         .WithClientSecret(clientSecret)
         .WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
         .Build();

     string[] scopes = new[] { $"{sqlDatabaseUrl}/.default" };
     var authResult = await confidentialClient.AcquireTokenForClient(scopes).ExecuteAsync();
     return authResult.AccessToken;
 }

Once connected to Dataverse, the query is executed, and the results are returned in a dataset. The dataset can then be easily converted to JSON using the function below.

 static string ConvertDataTableToJson(DataTable dataTable)
 {
     var list = new List<Dictionary<string, object>>();
     foreach (DataRow row in dataTable.Rows)
     {
         var dict = new Dictionary<string, object>();
         foreach (DataColumn column in dataTable.Columns)
         {
             dict[column.ColumnName] = row[column];
         }
         list.Add(dict);
     }

     return JsonSerializer.Serialize(list, new JsonSerializerOptions { WriteIndented = true });

 }

Summary

Once your function is created, you can run SQL queries from your browser or any application capable of making an HTTP call, including Power BI .

Caveats

• I have not covered how to authenticate calls to the Azure Function.
• The SQL query runs using the service principal’s Dataverse security role, which may expose data that the calling user does not have access to
• Additionally, there is no token caching in this approach.