# Connecting to Microsoft Dynamics 365 CRM
Microsoft Dynamics 365 is a product line of enterprise resource planning and customer relationship management intelligent business applications.
**Topics**
+ [
# Amazon Glue support for Microsoft Dynamics 365
](microsoft-dynamics-365-support.md)
+ [
# Policies containing the API operations for creating and using connections
](microsoft-dynamics-365-configuring-iam-permissions.md)
+ [
# Configuring Microsoft Dynamics 365 CRM
](microsoft-dynamics-365-configuring.md)
+ [
# Configuring Microsoft Dynamics 365 CRM connections
](microsoft-dynamics-365-configuring-connections.md)
+ [
# Reading from Microsoft Dynamics 365 CRM entities
](microsoft-dynamics-365-reading-from-entities.md)
+ [
# Microsoft Dynamics 365 CRM connection option reference
](microsoft-dynamics-365-connection-options.md)
+ [
# Limitations
](microsoft-dynamics-365-connector-limitations.md)
# Amazon Glue support for Microsoft Dynamics 365
Amazon Glue supports Microsoft Dynamics 365 as follows:
**Supported as a source?**
Yes. You can use Amazon Glue ETL jobs to query data from Microsoft Dynamics 365.
**Supported as a target?**
No.
**Supported Microsoft Dynamics 365 CRM API versions**
v9.2.
# Policies containing the API operations for creating and using connections
IAM policies
The following sample policy describes the required Amazon permissions for creating and using connections. If you are creating a new role, create a policy that contains the following:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"glue:ListConnectionTypes",
"glue:DescribeConnectionType",
"glue:RefreshOAuth2Tokens",
"glue:ListEntities",
"glue:DescribeEntity"
],
"Resource": "*"
}
]
}
```
------
You can also use the following managed IAM policies to allow access:
+ [ AWSGlueServiceRole ](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole) – Grants access to resources that various Amazon Glue processes require to run on your behalf. These resources include Amazon Glue, Amazon S3, IAM, CloudWatch Logs, and Amazon EC2. If you follow the naming convention for resources specified in this policy, Amazon Glue processes have the required permissions. This policy is typically attached to roles specified when defining crawlers, jobs, and development endpoints.
+ [ AWSGlueConsoleFullAccess ](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess) – Grants full access to Amazon Glue resources when an identity that the policy is attached to uses the Amazon Management Console. If you follow the naming convention for resources specified in this policy, users have full console capabilities. This policy is typically attached to users of the Amazon Glue console.
# Configuring Microsoft Dynamics 365 CRM
Before you can use Amazon Glue to transfer data from Microsoft Dynamics 365 CRM, you must meet these requirements:
## Minimum requirements
+ You have a Microsoft Dynamics 365 CRM developer account with ClientId and Secret.
+ Your Microsoft Dynamics 365 CRM account has API access with a valid license.
If you meet these requirements, you’re ready to connect Amazon Glue to your Microsoft Dynamics 365 CRM account. For typical connections, you don't need do anything else in Microsoft Dynamics 365 CRM.
# Configuring Microsoft Dynamics 365 CRM connections
**AUTHORIZATION\$1CODE Grant Type**
+ This grant type is considered “three-legged” OAuth as it relies on redirecting users to the third party authorization server to authenticate the user. It is used when creating connections via the Amazon Glue Console. The Amazon Glue Console will redirect the user to Microsoft Dynamics 365 CRM where the user must login and allow Amazon Glue the requested permissions to access their Microsoft Dynamics 365 CRM instance.
+ Users may opt to create their own connected app in Microsoft Dynamics 365 CRM and provide their own client id and client secret when creating connections through the Amazon Glue Console. In this scenario, they will still be redirected to Microsoft Dynamics 365 CRM to login and authorize Amazon Glue to access their resources.
+ This grant type results in a refresh token and access token. The access token is short lived, and may be refreshed automatically without user interaction using the refresh token.
+ For public Microsoft Dynamics 365 CRM documentation on creating a connected app for Authorization Code OAuth flow, see \$1 Microsoft Learn. [Microsoft App Registration](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/authenticate-oauth#app-registration).
Microsoft Dynamics 365 CRM supports OAuth2.0 authentication.
To configure a Microsoft Dynamics 365 CRM connection:
1. In Amazon Secrets Manager, create a secret with the following details. It is required to create a secret for each connection in Amazon Glue.
+ For AuthorizationCode grant type:
For customer managed connected app - Secret should contain the connected app Client Secret with `USER_MANAGED_CLIENT_APPLICATION_CLIENT_SECRET` as key.
1. In Amazon Glue Studio, create a connection under **Data Connections** by following the steps below:
1. When selecting a **Data Source**, select Microsoft Dynamics 365 CRM.
1. Select the **INSTANCE\$1URL** of the Microsoft Dynamics 365 CRM instance you want to connect to.
1. Select the IAM role that Amazon Glue can assume and has permissions for following actions:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"secretsmanager:DescribeSecret",
"secretsmanager:GetSecretValue",
"secretsmanager:PutSecretValue",
"ec2:CreateNetworkInterface",
"ec2:DescribeNetworkInterfaces",
"ec2:DeleteNetworkInterface"
],
"Resource": "*"
}
]
}
```
------
1. Select **Token URL** and **Authorization Code URL** to access your Microsoft Dynamics 365 CRM workspace.
1. Provide **User Managed Client Application ClientId** of your Microsoft Dynamics 365 CRM app.
1. Select the `secretName` which you want to use for this connection in Amazon Glue to put the tokens.
1. Select the network options if you want to use your network.
1. Grant the IAM role associated with your Amazon Glue job permission to read `secretName`. Choose **Next**.
1. In your Amazon Glue job configuration, provide `connectionName` as an **Additional network connection**.
# Reading from Microsoft Dynamics 365 CRM entities
**Prerequisites**
+ A Microsoft Dynamics 365 CRM object you would like to read from. You will need the object name such as contacts or accounts. The following table shows the supported entities.
**Supported entities**
| Entity | Can be Filtered | Supports Limit | Supports Order By | Supports Select \$1 | Supports Partitioning |
| --- | --- | --- | --- | --- | --- |
| Dynamic entity | Yes | Yes | Yes | Yes | Yes |
**Example**
```
dynamics365_read = glueContext.create_dynamic_frame.from_options(
connection_type="microsoftdynamics365crm",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "dynamic_entity",
"API_VERSION": "v9.2",
"INSTANCE_URL": "https://{tenantID}.api.crm.dynamics.com"
}
```
## Microsoft Dynamics 365 CRM Entity and Field Details
**Entities with dynamic metadata:**
Microsoft Dynamics 365 CRM provides endpoints to fetch metadata dynamically. Therefore, for dynamic entities, the operator support is captured at datatype level.
[\[See the AWS documentation website for more details\]](http://docs.amazonaws.cn/en_us/glue/latest/dg/microsoft-dynamics-365-reading-from-entities.html)
**Partitioning queries**
Microsoft Dynamics 365 CRM supports only field based partitioning.
Additional spark options `PARTITION_FIELD`, `LOWER_BOUND`, `UPPER_BOUND`, `NUM_PARTITIONS` can be provided if you want to utilize concurrency in Spark. With these parameters, the original query would be split into `NUM_PARTITIONS` number of sub-queries that can be executed by spark tasks concurrently.
+ `PARTITION_FIELD`: the name of the field to be used to partition query.
+ `LOWER_BOUND`: an inclusive lower bound value of the chosen partition field.
For Datetime, we accept the Spark timestamp format used in Spark SQL queries. Example of valid values: `"2024-01-30T06:47:51.000Z"`.
+ `UPPER_BOUND`: an exclusive upper bound value of the chosen partition field.
+ `NUM_PARTITIONS`: number of partitions.
Entity wise partitioning field support details are captured in below table:
| Entity Name | Partitioning Fields | DataType |
| --- | --- | --- |
| Dynamic Entity (Standard entity) | Dynamic DateTime fields which are queryable | createdon, modifiedon |
| Dynamic Entity (Custom entity) | createdon, modifiedon | createdon, modifiedon |
**Example**
```
dynamics365_read = glueContext.create_dynamic_frame.from_options(
connection_type="microsoftdynamics365crm",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "dynamic_entity",
"API_VERSION": "v9.2",
"instanceUrl": "https://{tenantID}.api.crm.dynamics.com"
"PARTITION_FIELD": "createdon"
"LOWER_BOUND": "2024-01-30T06:47:51.000Z"
"UPPER_BOUND": "2024-06-30T06:47:51.000Z"
"NUM_PARTITIONS": "10"
}
```
# Microsoft Dynamics 365 CRM connection option reference
The following are connection options for Microsoft Dynamics 365 CRM:
+ `ENTITY_NAME`(String) - (Required) Used for Read. The name of your object in Microsoft Dynamics 365 CRM.
+ `API_VERSION`(String) - (Required) Used for Read. Microsoft Dynamics 365 CRM Rest API version you want to use.
+ `SELECTED_FIELDS`(List) - Default: empty(SELECT \$1). Used for Read. Columns you want to select for the object.
+ `FILTER_PREDICATE`(String) - Default: empty. Used for Read. It should be in the Spark SQL format.
+ `QUERY`(String) - Default: empty. Used for Read. Full Spark SQL query.
+ `INSTANCE_URL`(String) - (Required) A valid Microsoft Dynamics 365 CRM Instance URL with the format: `https://{tenantID}.api.crm.dynamics.com`
+ `NUM_PARTITIONS`(Integer) - Default: 1. Used for Read. Number of partitions for read.
+ `PARTITION_FIELD`(String) - Used for Read. Field to be used to partition query.
+ `LOWER_BOUND`(String)- Used for Read. An inclusive lower bound value of the chosen partition field. Example: `2024-01-30T06:47:51.000Z`.
+ `UPPER_BOUND`(String) - Used for Read. An exclusive upper bound value of the chosen partition field. Example: `2024-06-30T06:47:51.000Z`.
# Limitations
The following are limitations for the Microsoft Dynamics 365 CRM connector:
+ In Microsoft Dynamics 365 CRM, record based partitioning is not supported as it does not support an offset parameter, and thus record based partitioning cannot be supported.
+ Pagination is set at a maximum of 500 records per page to avoid Internal Server exceptions from SaaS due to a combination of data size and rate limitations.
+ [SaaS documentation on pagination](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query/page-results?view=dataverse-latest)
+ [SaaS documentation on rate limits](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/api-limits?tabs=sdk)
+ Microsoft Dynamics 365 CRM supports `order by` on only parent fields for all entities. `order by` is not supported on sub-fields.
+ Both ASC and DESC directions are supported.
+ `order by` on multiple fields is supported.
+ Filtering on "createddatetime" field of the `aadusers` standard entity is throwing a bad request error from SaaS even though it supports filtration. There is no specific identification of any other entity having a similar issue due to the dynamic nature of the metadata and neither is the root cause known. Hence, it cannot be handled.
+ Complex object types, such as Struct, List, and Map do not support filtration.
+ Many fields that can be retrieved from a response have `isRetrievable` marked as `false` in dynamic metadata response. In order to avoid data loss, `isRetrievable` is set to `true` for all fields.
+ Field based partitioning will be supported on all entities when it adheres to the following criteria:
+ DateTime queryable fields should be present in standard entities or `createdon` and `modifiedon` fields (system-generated) in custom entities.
+ There is no exclusive identification of system generated fields or the nullable property from any SaaS metadata APIs, however it is a general practice that only the fields available by default, are filterable and non-nullable. Therefore, the above criterion of field selection is considered null safe and if it is filterable, it will be eligible for partitioning.