Expose Dynamics 365 APIs with Power Platform Custom Connectors

Break the Wall: Power Automate × Dynamics 365 with Production-Grade Custom Connectors

Tagline

When the standard connector stops—your automation doesn’t have to.

What you’ll learn

• How to identify missing capabilities in the standard Dynamics 365 connector

• A practical workflow to discover the exact Dynamics/Dataverse Web API endpoints you need

• Building a secure custom connector (OAuth2 via Azure AD, scopes, service principals)

• Production patterns: filtering, pagination, throttling, retries, and schema evolution

• Governance: DLP, RBAC, ownership, versioning, monitoring, and audit

---

The problem in one sentence

Standard connectors don’t always expose your custom tables, calculated fields, or flexible filters—custom connectors give you full API control with enterprise guardrails.

---

Reference blueprint (end-to-end)

1) Discover

• Map missing needs (custom tables, columns, advanced filters, actions).

• Use Dataverse Web API metadata, Solution Explorer, or API Inspector/Postman to confirm endpoints and logical names.

• Validate OData $filter, $select, $expand queries with sample payloads.

2) Secure

• Create Azure AD app registration (single-tenant, OAuth2).

• Grant minimal Graph/Dataverse app roles; decide user-delegated vs service principal.

• Configure redirect URL for custom connector; plan secret rotation.

3) Build

• In Power Platform, create Custom Connector (OpenAPI or from scratch).

• Define host, base path, auth, and actions (GET/POST/PATCH), including query/body schemas.

• Add policies: retry with exponential back-off, pagination, set default headers, timeouts.

4) Govern

• Assign owner, set environments (Dev/Test/Prod), and DLP policy classification.

• Restrict usage via RBAC/security groups; document scopes and data domains.

• Establish versioning (v1, v2…), change log, and deprecation timelines.

5) Operate

• Monitor flow runs, connector telemetry; alert on 4xx/5xx spikes and long runtimes.

• Add circuit-breakers, queued replays for 429 throttling; track consumption.

• Quarterly review for permissions, secrets, and API changes.

---

API discovery quick guide

• Find logical names: In your solution, check table/column logical names (e.g., new_pricetierlevelid).

• Test queries with Postman:

  • GET .../api/data/v9.2/new_warrantyclaims?$select=...&$filter=modifiedon ge {timestamp}

  • Use $expand for lookups; $count + pagination (@odata.nextLink).

• Calculated/rollup fields: verify they’re materialized and filterable; otherwise compute server-side (FetchXML) or downstream.

• Actions/Functions: call bound/unbound actions (e.g., CalculatePrice) when CRUD isn’t enough.

---

Security & privacy guardrails

• OAuth2 (Azure AD), no API keys.

• Least privilege: limit app roles to specific tables/operations; prefer service principals for background jobs.

• User attribution: use delegated permissions when business needs a human audit trail.

• DLP: classify connector as Business; block cross-boundary flows to personal services.

• Data minimization: $select only needed columns; mask PII downstream; apply environment RLS where relevant.

---

Production patterns (copy-paste worthy)

Exponential back-off for 429/5xx

• Initial delay 2–5s; double up to cap (e.g., 60s); max retries 5–7; log correlation IDs.

Pagination

• Honor @odata.nextLink; aggregate pages server-side where possible; set page size (e.g., $top=5000) within limits.

Delta loads

• Filter by modifiedon ge {lastRunUtc}; persist last successful watermark; handle clock skew.

Idempotency

• Upserts (PATCH with If-Match/alternate key) to avoid duplicates; store source IDs.

Schema evolution

• Detect missing/extra fields; soft-fail unknowns; maintain v1/v2 connectors during migration.

---

Example use cases (and how the custom connector fixes them)

1. Custom table sync (WarrantyClaims)

• Action: GET /new_warrantyclaims?$select=claimid,status,amount&$filter=modifiedon ge {ts}

• Flow: Recurrence → Get claims → For Each → Upsert into Dataverse/ERP.

1. Advanced filter for “VIP Accounts”

• Action: GET /accounts?$filter=new_vipstatus eq true and statuscode eq 1

• Flow: Trigger on schedule → Only act on VIP, active accounts → Notify CSMs.

1. Invoke server action (Recalculate)

• Action: POST /CalculateRevenue (unbound) with parameters (date range, segment).

• Flow: On demand/runbook → Call action → Store result in reporting table.

---

Monitoring & reliability

• Dashboard: success rate, avg latency, retries, top error codes, throughput per action.

• Alerts: sustained 429s, auth failures, >P95 latency, schema mismatches.

• Logs: include request IDs, partition keys, and watermarks in telemetry for replay.

• Resilience: queue overflow paths (e.g., Dataverse table/Azure Queue) on repeated failure.

---

Governance & lifecycle

• Owner of record (app + connector), documented purpose, data domains, consumers.

• Promotion workflow: PRD only from signed-off artifacts; env-specific secrets via connection references.

• Review cadence: quarterly access review, secret rotation, API deprecation checks.

• Decommissioning: mark as deprecated, block new connections, sunset after migration.

---

10-step fast start (week plan)

1. List gaps in the standard connector (tables/fields/filters/actions).

2. Confirm Web API endpoints & queries in Postman.

3. Register Azure AD app; decide delegated vs app-only.

4. Build custom connector (Dev); define actions & schemas.

5. Add policies: retries, pagination, headers, timeouts.

6. Create connection references per environment.

7. Ship a pilot flow; validate volume, filters, and idempotency.

8. Add monitoring + alerts; document runbooks.

9. Promote to Test/Prod; lock down via DLP/RBAC.

10. Publish v1 docs; plan v2 (known limits, roadmap).

---

Common pitfalls (and fixes)

• 429 throttling → implement back-off + batching; spread schedules.

• Over-wide permissions → least-privilege app roles; split connectors by domain.

• Hardcoded secrets → use service principals + managed secrets; rotate quarterly.

• Connector sprawl → central catalog, owners, versioning policy.

• Schema breaks → contract tests in Dev; dual-run v1/v2 during API changes.

---

FAQs

Q: Can I reach custom/ISV tables?
A: Yes—use their logical names via the Web API; add actions in the connector with proper schemas.

Q: Delegated or app-only?
A: App-only for backend/scheduled jobs; delegated when you need user-level audit/control.

Q: How do I stay compliant with DLP?
A: Classify as Business, block cross-boundary connectors, restrict usage by security group, and log exports.

Q: What about performance at scale?
A: Batch reads, delta queries, back-off on 429s, parallelism with care; monitor and tune $top/filters.