Connecting Azure Data Factory to Dynamics 365 CE sql Connection

Question

Connecting Azure Data Factory to Dynamics 365 CE sql Connection

Dixit Jain 20

Hi,

In Azure Data Factory, we can use Dynamics 365 Connector for executing FetchXml.

Wanted to know steps to connect ADF with Dynamics 365 CE Database, so that we can execute Sql query on Dynamics 365 CE instance.

Let me know if you have any question.

Regards,

Dj

Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2025-05-16T10:30:50.4033333+00:00
Hi @Dixit Jain
Thank you for your question regarding connecting Azure Data Factory (ADF) to Dynamics 365 Customer Engagement (CE).

Currently, Azure Data Factory does not support direct SQL queries against the Dynamics 365 CE database (Dataverse) due to the underlying architecture and security model of Dataverse. Instead, ADF provides a Dynamics 365 (or Dataverse) connector, which supports FetchXML queries - the recommended way to retrieve data from Dynamics 365 CE.

Here’s how you can connect ADF to Dynamics 365 CE using the supported method:

Option 1: Use the Dynamics 365 (or Dataverse) Connector

Create a Linked Service in ADF:

Go to Manage > Linked Services > New.

Select Dynamics 365 (or Common Data Service for Apps).

Provide the service URL, and authentication method (typically OAuth2 with Azure AD).

Ensure the app registration has the required API permissions for Dataverse (e.g., user_impersonation scope).

Create a Dataset:

Use the linked service you just created.

Provide the entity (table) name, or use a FetchXML query for more advanced filtering.

Use the Copy Activity or Data Flows:

In your pipeline, use a Copy Data activity or Mapping Data Flows to extract data.

For more information you can refer this official article: https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider&tabs=data-factory

Note: Direct SQL queries against Dataverse are not supported through ADF due to Microsoft’s platform design. SQL access (read-only) is available via TDS endpoint, but this is limited and not currently supported natively in ADF.

Option 2: Alternative Workaround (if SQL access is a hard requirement)

Use Azure Synapse Link for Dataverse to replicate Dataverse data into Azure Synapse Analytics or Azure Data Lake.

Then, use ADF to connect to Synapse SQL or the lake for SQL-based querying and transformation.

For more information you can refer this official article: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2025-05-19T12:27:07.3166667+00:00

@Dixit Jain We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dixit Jain 20 Reputation points

2025-05-20T03:51:50.59+00:00

Hi, I was working on getting access & Synpase setup. It seems it is the way forward.

Thanks for the information.

Accepted answer

1 additional answer

Your answer

Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2025-05-19T12:27:07.3166667+00:00

@Dixit Jain We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dixit Jain 20 Reputation points

2025-05-20T03:51:50.59+00:00

Hi, I was working on getting access & Synpase setup. It seems it is the way forward.

Thanks for the information.

Answer 1

Michele Ariis 2,040 MVP

Hi, to run T-SQL from ADF against Dataverse, first enable the TDS endpoint in Power Platform Admin Center (make sure ports 1433/5558 are open outbound), then create an app registration in Entra ID and add it as an Application User in Dataverse with read permissions; set up a Self-Hosted IR (recommended for most setups), create a SQL Server linked service in ADF pointing to <org>.crm.dynamics.com,5558 using Azure AD Service Principal auth, and finally use that connection in Copy or Data Flow activities - remember the TDS endpoint is read-only, times out after 5 minutes, doesn’t support all data types, and requires the org unique name as the DB name.

Zhenxing Tang 0 Reputation points

2025-06-09T07:12:36.6066667+00:00

There is no Azure AD Service Principal auth in SQL Server linked service. Only SQL authentication, Windows authentication and User-assigned managed identity are supported.

Answer 2

Nandan Hegde 36,151 MVP Volunteer Moderator

Based on my understanding, there is no direct way to execute SQL queries on Dynamics 365 CE database.

Neither in ADF nor any other tool in direct way.

You can set up Azure Synapse link and query that data from Synapse in form of SQL
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse

Share via

Connecting Azure Data Factory to Dynamics 365 CE sql Connection

1 additional answer

Your answer