Connect to the Microsoft Dataverse via Databricks & Web API

Summary

It’s not possible to connect to the Microsoft Dataverse directly in Databricks. Some options include ingesting the Dataverse tables into Azure Data Lake and then retrieving them from inside a notebook. These options require additional resources.

In this post, I’ll document how to connect to the Microsoft Dataverse using Python and the Dataverse Web API.

Setup Procedure

1. Create a Client Endpoint Azure App Registration.
2. Run Python Web API commands in Databricks.

Create a Client Endpoint

Register a new Server Endpoint Application Registration called Dataverse-Client-Endpoint

Authentication

Under Authentication, Platform Configuration, create a new Web configuration with the following properties:

• Redirect URIs: https://localhost/auth
• Access tokens (used for implicit flows) property set to enabled.
• ID tokens (used for implicit and hybrid flows) property set to enabled.

Certificates & secrets

Create a new Certificate & secrets. Make a note of the secret value.

1. Client Id: d3813157-b011-440c-816e-9b06278b64ba
2. Secret: ICM8Q~VokJll4erWvL5FqoxVnezwRbxscFWAiaJl

API permissions

Under API permissions, add the Dynamics CRM permissions. Ensure the following is selected:

• Delegated permissions selected.
• Permissions, user_impersonation selected.

Grant Admin permissions to enable the use of this API.

Expose an API

There are two Token endpoints in Azure:

1. OAuth 2.0 token endpoint (v1)
2. OAuth 2.0 authorization endpoint (v2)

V1 requires that you specify the resource name when requesting a token.
V2 requires that you provide a scope when requesting a token.

Each method is invoked as follows:

Token Endpoint V1

Token Endpoint V2

Note that Endpoint v2 requires a scope to be provided. The scope is provided following the environment URL with the /.default extension.

Final Endpoint Details

The final endpoint details are as follows:

Call the Dataverse Web API

1. GET request to receive the JWT.
2. GET request to retrieve Customer data using the JWT.

Get JWT Token using Python

Using the details from the Final Endpoint table, an HTTP GET request can be made. Notice that the payload variables contain the client_id, secret and scopes.

import http.client

conn = http.client.HTTPSConnection("login.microsoftonline.com")
payload = 'grant_type=client_credentials&client_id=d3813157-b011-440c-816e-9b06278b64ba&client_secret=ICM8Q~VokJll4erWvL5FqoxVnezwRbxscFWAiaJl&scope=https%3A%2F%2Forg40ebe5b2.crm11.dynamics.com%2F.default'
headers = {
  'Content-Type': 'application/x-www-form-urlencoded',
  'Cookie': 'esctx=AQABAAAAAAD--DLA3VO7QrddgJg7WevrVQZAIULi8S0RIu1BUGqdV9w_mFA1rWYEchBq2lCbgs7OhVLyuvYdVXIwjBWMaswMuBHmq5RwjYUYNV00t4zTjQ5UAPrCPpQArIQ_gMMYkrlcSR_7NbY6yq-D3XEW_RtsT6SjtdMQAj15kneOCp2IgBAqUIT-2Q0t9eS6Zl4ZAIkgAA; fpc=At0JJ0iIgalGuuMNyGPbqvboLDHCAQAAADTRQNoOAAAA; stsservicecookie=estsfd; x-ms-gateway-slice=estsfd'
}
conn.request("GET", "/dcd1f5f0-289f-4a92-83a4-000000/oauth2/v2.0/token", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

{
    "token_type": "Bearer",
    "expires_in": 3599,
    "ext_expires_in": 3599,
    "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImpTMVhvMU9XRGpfNTJ2YndHTmd2UU8yVnpNYyIsImtpZCI6ImpTMVhvMU9XRGpfNTJ2YndHTmd2UU8yVnpNYyJ9.eyJhdWQiOiJodHRwczovL29yZzQwZWJlNWIyLmNybTExLmR5bmFtaWNzLmNvbSIsImlzcyI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0L2RjZDFmNWYwLTI4OWYtNGE5Mi04M2E0LTY2ZmYyMjM1MGQ4Yy8iLCJpYXQiOjE2NTU2MjU5OTMsIm5iZiI6MTY1NTYyNTk5MywiZXhwIjoxNjU1NjI5ODkzLCJhaW8iOiJFMllBZ3VXNm43WThFSk9YVG4rdStaYlAvd01BIiwiYXBwaWQiOiJkMzgxMzE1Ny1iMDExLTQ0MGMtODE2ZS05YjA2Mjc4YjY0YmEiLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9kY2QxZjVmMC0yODlmLTRhOTItODNhNC02NmZmMjIzNTBkOGMvIiwib2lkIjoiOThkZWJjNTktYjFiMi00MDM1LThmMWQtOWM3MTRiMDQ5ZTVkIiwicmgiOiIwLkFVY0E4UFhSM0o4b2trcURwR2JfSWpVTmpBY0FBQUFBQUFBQXdBQUFBQUFBQUFCSEFBQS4iLCJzdWIiOiI5OGRlYmM1OS1iMWIyLTQwMzUtOGYxZC05YzcxNGIwNDllNWQiLCJ0aWQiOiJkY2QxZjVmMC0yODlmLTRhOTItODNhNC02NmZmMjIzNTBkOGMiLCJ1dGkiOiI1VWtIcGhHTHNVU1d0VmhZX3hBVUFBIiwidmVyIjoiMS4wIn0.LzXqdOBYjklqt7zfcASI39ur08u-lYPTJ0NvAVA4oFMERG0dUjcIvTqAAKLbUINlOVMKCICa1nUxlU1rGhfw0xAUR9-vIk4okJJKfLJ70byEMwTWH5yJW9m3cbck4XmYHwdH-HHdBQFdqwRcsK3Ew3IUDkDAaUsnBIsALLVpCi_J0eQk-EbVGKmiGY9ekHnXnIewXOBBB-nB6Ygst0vv8D72DkrJZIfs7YqTqdPhzr-bd_D2baaIYPURGrJsJxhnVeW6iHhd9BXsaTZOXr2dVvgREv1q07fJC01pHBOXMjUUTpZ107e2MafOVobwpFBtqlDdbm1xVM_zdqMfV2cmMQ"
}

Get Dataverse Data using Python

Using the Bearer Token from above, a new HTTP GET request can be made with the following properties:

GET: https://dynamicsdev01.crm11.dynamics.com/api/data/v9.2/accounts?$select=name
Authorization: 'Bearer {token}'

import http.client

conn = http.client.HTTPSConnection("dynamicsdev01.crm11.dynamics.com")
payload = ''
headers = {
  'Authorization': 'Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImpTMVhvMU9XRGpfNTJ2YndHTmd2UU8yVnpNYyIsImtpZCI6ImpTMVhvMU9XRGpfNTJ2YndHTmd2UU8yVnpNYyJ9.eyJhdWQiOiJodHRwczovL29yZzQwZWJlNWIyLmNybTExLmR5bmFtaWNzLmNvbSIsImlzcyI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0L2RjZDFmNWYwLTI4OWYtNGE5Mi04M2E0LTY2ZmYyMjM1MGQ4Yy8iLCJpYXQiOjE2NTU2MjU5OTMsIm5iZiI6MTY1NTYyNTk5MywiZXhwIjoxNjU1NjI5ODkzLCJhaW8iOiJFMllBZ3VXNm43WThFSk9YVG4rdStaYlAvd01BIiwiYXBwaWQiOiJkMzgxMzE1Ny1iMDExLTQ0MGMtODE2ZS05YjA2Mjc4YjY0YmEiLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9kY2QxZjVmMC0yODlmLTRhOTItODNhNC02NmZmMjIzNTBkOGMvIiwib2lkIjoiOThkZWJjNTktYjFiMi00MDM1LThmMWQtOWM3MTRiMDQ5ZTVkIiwicmgiOiIwLkFVY0E4UFhSM0o4b2trcURwR2JfSWpVTmpBY0FBQUFBQUFBQXdBQUFBQUFBQUFCSEFBQS4iLCJzdWIiOiI5OGRlYmM1OS1iMWIyLTQwMzUtOGYxZC05YzcxNGIwNDllNWQiLCJ0aWQiOiJkY2QxZjVmMC0yODlmLTRhOTItODNhNC02NmZmMjIzNTBkOGMiLCJ1dGkiOiI1VWtIcGhHTHNVU1d0VmhZX3hBVUFBIiwidmVyIjoiMS4wIn0.LzXqdOBYjklqt7zfcASI39ur08u-lYPTJ0NvAVA4oFMERG0dUjcIvTqAAKLbUINlOVMKCICa1nUxlU1rGhfw0xAUR9-vIk4okJJKfLJ70byEMwTWH5yJW9m3cbck4XmYHwdH-HHdBQFdqwRcsK3Ew3IUDkDAaUsnBIsALLVpCi_J0eQk-EbVGKmiGY9ekHnXnIewXOBBB-nB6Ygst0vv8D72DkrJZIfs7YqTqdPhzr-bd_D2baaIYPURGrJsJxhnVeW6iHhd9BXsaTZOXr2dVvgREv1q07fJC01pHBOXMjUUTpZ107e2MafOVobwpFBtqlDdbm1xVM_zdqMfV2cmMQ',
  'Cookie': 'ARRAffinity=0c7e87688228727814ca17753f54df75881205efbf0adc6a2ed7acd72bf694de; ReqClientId=929223df-e76c-4f91-8acb-4acc430fd046; orgId=ce5e89bb-7fe7-4dcb-9fc1-4a18cb8dbf72'
}
conn.request("GET", "/api/data/v9.2/accounts?$select=name", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

{
    "@odata.context": "https://dynamicsdev01.crm11.dynamics.com/api/data/v9.2/$metadata#accounts(name)",
    "value": [
        {
            "@odata.etag": "W/\"2996295\"",
            "name": "BBC",
            "accountid": "a1e22540-a2ef-ec11-bb3c-0022484286e7"
        },
        {
            "@odata.etag": "W/\"2996302\"",
            "name": "Microsoft",
            "accountid": "73d36a4f-a2ef-ec11-bb3c-0022484286e7"
        },
        {
            "@odata.etag": "W/\"2996311\"",
            "name": "Amazon",
            "accountid": "864ef955-a2ef-ec11-bb3c-0022484286e7"
        }
    ]
}