Connect Jet Analytics to Dynamics CRM with CData

Prerequisites

Before starting the connection:

• Make sure that you have the Dynamics CRM provider downloaded into your project and updated to the latest version. See Managing CData Providers.
• Create an App. See also Steps to Create a Custom OAuth App.

Follow the steps below to connect Dynamics CRM with CData.

Connect to Dynamics CRM with CData

1. Sign in to your account through the Azure portal.
2. Create your app:
   1. In the left-hand navigation pane, select Microsoft Entra ID.
   2. Select App Registrations.
   3. Click Add.
   4. Fill out the app registration settings:
      1. Name: Choose the name of your app.
      2. Supported account types: Select Any Azure AD Directory - Multi Tenant.
      3. Redirect URL: Select the provider's default, for example, http://localhost:33333, or you may use your Organizational URL for your Dynamics instance (https://<company>.crm4.dynamics.com/) and set CallbackURL to the exact reply URL you defined.
   5. Select Register. The app is registered in Azure. Make sure to note down the Application ID.
3. Create the OAuth key:
   1. In the Azure portal, go to the Certificates & Secrets section.
   2. Create a Client Secret for the app.
   3. Select a duration.
   4. Save the key. The value for the key is displayed once. Make sure to note down the key.
4. Enter the authentication information in Dynamics CRM and the Jet Data Manager:
   1. Set OAuthClientSecret to the key value that you have just created.
   2. Set OAuthClientId to the Application Id of the app that you have created.
5. Set up Dynamics CRM
   1. Select Dynamics CRM > Delegated Permissions > user_impersonation.
   2. Save your changes.
   3. Optionally, if you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.
   4. Create an application user. You will need to give this user a role that enables them to interact with entities in CRM.
   5. Go to https://<your-instance>.crm1.dynamics.com/
   6. Navigate to Settings > Security > Users.
   7. Set the Application ID from the App Registration step.
   8. Use the information that you have created above and place them in the fields as shown in the figure below to set up the CData Data Source.

      

   9. Click Authorize OAuth. A web browser should open with a success message.
   10. Click Test Connection to confirm that you have connected successfully.
   11. Click OK to apply your changes.

Miscellaneous Settings

A common change that needs to be made for Dynamics CRM connections is the default string length (2000).  To change this, you can enter 'DefaultColumnSize=8000' into the Other row.  Setting the default size to 8000 will cause these fields to become an nvarchar(MAX) data type when you synchronize.