Before you attempt this you need to add the CRM provider to your list of available sources.

Go through this guide before you start.

Add a CData data source

Contents

• CData setup
  • Connect with user account & password
  • Connect with OAuth
  • Other settings
• Convert from obsolete CRM to CData
• Use a Dynamics365 Sales CData provider to connect to CRM instead
• Troubleshooting

CData setup

Add a CData data source and choose the DynamicsCRM provider.

The first step in setting up a CData connector should always be to click the ? in the top right corner.

Dynamics CRM guide from CData

Then check the connection info and verify if there is some specifics you need to look at before starting.

You also need to find the new server name to add, now that disco.crm.dynamics.com is not working anymore. You should be able to see it by going to this webpage https://home.dynamics.com/

For me it is not necessarily called CRM, but if you hover above them you can see the link to the server name.

Connect with user account & password

• You need to add the URL to the Dynamics CRM page. Use the one from the https://home.dynamics.com/ link the usual name is https://organizationname.crm.dynamics.com
• Set the CRM version to be equal CRMOnline if you have an online version of it. Otherwise choose CRM2011+
• Add a user that is not using 2 Factor Authentication in the User & Password fields.

See a setup below.

Click Test Connection to verify you have access.

Connect with OAuth

Before starting with this you need to create an App.

Steps to Create a Custom OAuth App

Follow the steps below to obtain the OAuth values for your app, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory then App Registrations and click the Add button.
3. Enter an app name and set the radio button for "Any Azure AD Directory - Multi Tenant". Then set the redirect url to something such as http://localhost:33333, the provider's default. Or, set a different port of your choice and set CallbackURL to the exact reply URL you defined.
4. After creating the app, go to the Certificates & Secrets section, create a Client Secret for the app and select a duration.
5. After you save the key, a value for the key is displayed once. Set OAuthClientSecret to this key value. Set OAuthClientId to the Application Id.
6. Select Dynamics CRM -> Delegated Permissions -> user_impersonation.
7. Save your changes.
8. If you have selected to use permissions that require admin consent (such as the Application Permissions), you may grant them from the current tenant on the API Permissions page.
9. Go to the Dynamics CRM instance and create an application user. We need to give this user a role to interact with entities in CRM. Go to https://your-instance.crm1.dynamics.com/, then navigate to Settings => Security => Users. Set the Application ID from the App Registration step.

With the app created you can gain access with those settings like so.

When you have added these options, click on the Authorize OAuth button and see that a web browser opens with a success message like this below.

Now click on Test Connection to see that you have access.

Consider changing the OAuthSettings.txt file default location to a specific one. When using scheduled execution the account running the service will look at its own AppData folder and locate no settings file otherwise. Additionally be sure that user accounts are allowed to create and edit files in the folder where it is stored.

Other settings

There are some choices that can be made in regards to the options in the Misc area. One specific change that is relevant for CRM is the option to change the String default length of 2000 to something else.

In this case I can add a DefaultColumnSize=value option to increase or decrease this size. Setting it to 8000 like below will make all these fields become NVarChar(MAX) when you synchronize.

Each field is explained here in the CData Guide.

Connection String Options

Convert from obsolete CRM to CData

If you are using an Custom Data source pointing at the CRM provider or an CRM Adapter data source in the Business Unit it will not work anymore as explained.

To continue using CRM there is a method to continue.

First locate the data source, right click on it, go to Change Provider and then click on Change to CData Data Source

Then you can change it to be a CData Dynamics CRM provider.

When done and you synchronize you will loose the selected tables from the old source. So do not attempt this if you use history or have any transformations added to some of the selected tables.

If so add the Dynamics CRM data source besides the original one and use the Guard On deployment and execution feature to avoid it losing the data.

The tables have the same name as before, so you should be able to map them in to the DWH with no changes.

Using the following feature will make it faster.

Use Dynamics365 Sales CData provider to connect to CRM instead

Dynamic 365 Sales is the new name for Dynamics CRM. You can if you use the OAuth method explained above use the same settings to connect crm data with the D365 Sales provider.

A thorough guide can be found here. Connect to Dynamics 365 Sales

Troubleshooting

As mentioned before using the change of the default column size is a change that will likely be necessary. Despite that using Data Type overrides on specific fields, names or tables will help getting the data through.

Use the misc options, if necessary and read the CData guide that are linked for explanations of the options.