Table of Contents

Background

Despite the very confusing branding, I like Microsoft Dataverse a lot. It’s basically a SQL server with a web UI and HTTP APIs built-in. And it is an actual SQL server too, you can query a read-only interface easily with SQL Server Management Studio. However, querying the SQL interface with Python eluded me, and I only recently figured it out. There are expensive commercial offerings available, but really, you can do it easily with only a few lines of code. I somehow stumbled across this GitHub gist which was the “Eureka!” moment for me.

Solution

You will need to install the mssql-python package to connect to the SQL server and the azure-identity or the msal package to obtain an access token.

Obtaining a Microsoft authentication token is its own complicated subject, and varies a lot depending on whether the application is being run directly by a user versus on a web application backend, whether you can utilize an authentication broker, and other factors. The below code sample shows one of the simplest possible ways.

 1# /// script
 2# dependencies = ["mssql-python", "azure-identity"]
 3# ///
 4
 5import struct
 6
 7import mssql_python
 8from azure.identity import InteractiveBrowserCredential, TokenCachePersistenceOptions
 9
10# Dataverse/Dynamics Hostname
11DATAVERSE_HOST = "{org}.crm.dynamics.com"
12# Magic constant
13SQL_COPT_SS_ACCESS_TOKEN = 1256
14# SQL port number
15SQL_PORT = 1433
16
17# Acquire access token. There are many ways, this is just a simple interactive method.
18cache_options = TokenCachePersistenceOptions()
19credential = InteractiveBrowserCredential(cache_persistence_options=cache_options)
20# This scope is important. This differs from the scope required for the HTTP APIs
21token = credential.get_token(f"https://{DATAVERSE_HOST}/.default")
22
23# Encode token for MS SQL server
24token_bytes = token.token.encode("utf-16-le")
25token_struct = struct.pack(f"<I{len(token_bytes)}s", len(token_bytes), token_bytes)
26
27# Connect with access token
28connection = mssql_python.connect(
29    f"Server={DATAVERSE_HOST},{SQL_PORT};Encrypt=yes;",
30    attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct},
31)
32cursor = connection.cursor()
33
34# Execute a query
35query = "SELECT TOP 10 * FROM account"
36cursor.execute(query)
37rows = cursor.fetchall()
38
39# Show results
40for row in rows:
41    print(row)

Conclusion

This has unlocked a whole new world of possibilities for me. Instead of manually running queries with SQL Server Management Studio, I can now programmatically run them and do things with the outputs, infinitely faster and more powerful than the HTTP APIs. I really wish Microsoft would document this better and not leave developers making educated guesses off the Azure SQL instructions.

References