feat: Microsoft Dynamics 365 CRM / Dataverse Integration

[jrosskopf]

Summary

Add native support for connecting to Microsoft Dynamics 365 CRM (Customer Engagement / Sales) and Dataverse from DuckDB, enabling users to authenticate and query CRM data using SQL.

Relationship to Business Central Integration (#5)

This issue shares significant implementation overlap with #5 (Business Central Integration). Both use:

• Microsoft Entra ID OAuth2 authentication (identical token endpoint and flow)
• OData v4 API compliance (same query capabilities)
• Same scope pattern for tokens ({resource_url}/.default)

Recommended approach: Implement a shared microsoft_entra or azure_ad authentication provider that both Business Central and CRM/Dataverse integrations can use. This avoids code duplication and ensures consistent authentication behavior.

Background

Microsoft Dynamics 365 CRM (also known as Customer Engagement, Sales, or Dataverse) is a cloud-based CRM platform widely used for sales, marketing, customer service, and field service operations. It exposes data through:

• Dataverse Web API (OData v4 compliant)
• Standard entities (accounts, contacts, leads, opportunities, etc.)
• Custom entities (user-defined tables)

User Stories

1. Service-to-Service Authentication (Primary)

As a data engineer, I want to configure automated access to Dynamics 365 CRM using client credentials, so that I can build ETL pipelines without user interaction.

Acceptance Criteria:

• Create a secret with client_id, client_secret, tenant_id, and environment_url
• Authenticate using OAuth2 client credentials flow against Microsoft Entra ID
• Automatically obtain and refresh tokens as needed

2. Interactive User Authentication

As an analyst, I want to authenticate to Dynamics 365 CRM using my Microsoft account, so that I can query data with my user permissions.

3. Entity Discovery

As a user, I want to discover all available entities in my Dataverse environment, so that I can explore the data model.

4. Entity Schema Discovery

As a user, I want to describe the schema of a specific entity, so that I understand the available fields.

5. Data Reading with Pushdown

As an analyst, I want to query Dynamics 365 CRM data using SQL with filter pushdown, so that I can efficiently retrieve only the data I need.

Technical Context

Dataverse Web API Structure

Base URL Pattern:

https://{organization}.crm.dynamics.com/api/data/v9.2/

Authentication (Same as Business Central #5)

Microsoft Entra ID OAuth2:

• Token endpoint: https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token
• Scope: {environment_url}/.default
• Client credentials flow for S2S automation

Core CRM Entities

Sales: accounts, contacts, leads, opportunities, quotes, orders, invoices
Marketing: campaigns, lists, campaignresponses
Customer Service: incidents (cases), knowledgearticles, queues
Common: systemusers, teams, businessunits, annotations, activities

Query Capabilities (Same as Business Central)

• $filter, $select, $expand, $top, $skip, $count, $orderby

Example Usage (Conceptual)

-- Create secret for S2S authentication
CREATE SECRET crm_secret (
    TYPE dataverse,
    PROVIDER client_credentials,
    CLIENT_ID 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
    CLIENT_SECRET 'your-client-secret',
    TENANT_ID 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
    ENVIRONMENT_URL 'https://myorg.crm.dynamics.com'
);

-- Discover all available entities
SELECT * FROM crm_show_entities(secret := 'crm_secret');

-- Describe entity schema
SELECT * FROM crm_describe('accounts', secret := 'crm_secret');

-- Query accounts with filter pushdown
SELECT name, emailaddress1, revenue
FROM crm_read('accounts', secret := 'crm_secret')
WHERE statecode = 0;

-- Query opportunities with expansion
SELECT * FROM crm_read('opportunities', secret := 'crm_secret', expand := 'customerid_account')
WHERE estimatedclosedate >= '2024-01-01';

Implementation Strategy

Shared Components with Business Central (#5)

Component	Reuse Strategy
Microsoft Entra ID OAuth2	Create shared MicrosoftEntraAuthProvider class
Token refresh logic	Shared token storage and refresh mechanism
OData v4 query building	Existing odata_client infrastructure
Predicate pushdown	Existing odata_predicate_pushdown_helper

CRM-Specific Components

Component	Description
URL builder	CRM-specific URL patterns ({org}.crm.dynamics.com/api/data/v9.x/)
Entity discovery	Query EntityDefinitions metadata endpoint
Secret type	TYPE dataverse with ENVIRONMENT_URL parameter

Out of Scope

• Write operations (POST, PATCH, DELETE)
• FetchXML query support
• On-premises deployments

References

• Dataverse Web API Overview
• Use OAuth authentication with Dataverse
• Dataverse Entity Reference

[RaulRoPer]

SELECT * FROM crm_show_entities(secret := 'crm_secret'); break duckdb

[jrosskopf]

Implementation shipped ✓

All functions described in this issue are implemented and available in production.

Shipped functions

Function	Description
crm_show_entities([secret])	Discovers all entity sets exposed by the Dataverse Web API for the configured environment. Returns entity logical name, entity set name, display name, and description.
crm_describe(entity, [secret])	Describes the schema of a specific Dataverse entity — attribute logical names, types, display names, required level, and whether the attribute is the primary key.
crm_read(entity, [secret])	Reads data from any Dataverse entity with full OData v4 filter/select pushdown and automatic pagination via @odata.nextLink.

Key design decisions

• TYPE dataverse secret: dedicated secret type that stores TENANT_ID, CLIENT_ID, CLIENT_SECRET, and ENVIRONMENT_URL (https://{org}.crm.dynamics.com). The scope is derived automatically from the environment URL.
• OData v4 reuse: crm_read shares the same OData v4 infrastructure used by Business Central, SAP Datasphere, and the generic odata_read — predicate pushdown, $select, and pagination are all handled by the common layer.
• Metadata via EntityDefinitions: crm_show_entities and crm_describe query the Dataverse EntityDefinitions and Attributes metadata endpoints rather than the OData $metadata XML, which is more reliable for large environments with many custom entities.

Example

CREATE SECRET crm_secret (
    TYPE dataverse,
    PROVIDER client_credentials,
    TENANT_ID '...', CLIENT_ID '...', CLIENT_SECRET '...',
    ENVIRONMENT_URL 'https://myorg.crm.dynamics.com'
);

SELECT * FROM crm_show_entities(secret := 'crm_secret');
SELECT * FROM crm_describe('account', secret := 'crm_secret');

SELECT name, emailaddress1, revenue
FROM crm_read('accounts', secret := 'crm_secret')
WHERE statecode = 0;

Required Azure app permissions

Dynamics CRM — user_impersonation (delegated) or a Dataverse application user with an appropriate security role assigned (for client credentials / S2S).

[jrosskopf]

Reopening: code is implemented and ships in the extension, but has never been tested against a live Dataverse/Dynamics 365 CRM tenant.

Missing before this can be closed:

• Integration test file test/sql/dataverse_integration.test with real API calls
• .env.dataverse with the required env vars
• make test_debug_dataverse Makefile target (analogous to make test_debug_ms)
• Manual verification that crm_show_entities, crm_describe, and crm_read return correct data against a real tenant