Setup a CRM Connection using CRM Connection Manager

SSIS Integration Toolkit CRM Connection Manager is an SSIS connection manager that can be used to establish connections with the Microsoft Dynamics CRM Server. The below documentation can also be followed for establishing a connection to Microsoft Common Data Service (CDS) using CDS Connection Manager.

The CRM connection manager allows you to specify how you want to connect to your CRM server. 

To add a CRM connection to your SSIS package, right-click in Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window where you can add the "DynamicsCRM" item.

new connection

Add CRM Connection Manager

The CRM Connection Manager contains the following three pages which configures how you want to connect to the Microsoft Dynamics CRM server.

  • CRM Server
  • Advanced Settings
  • More Info

CRM Server page

The CRM Server page of the CRM Connection Manager allows you to specify general settings for the connection.

CRM Connection Manager

Service Endpoint

The Service Endpoint option allows you to specify the service endpoint that you want to use in order to connect to the Microsoft Dynamics 365CE/CRM server. We currently support the following options:

  • WebAPI (Dynamics 365 CE, CRM 2016, Online or On-Premise)
  • SOAP 2011 (Dynamics 365 CE, CDS, CRM 2016, 2015, 2013, 2011)
  • SOAP 2007 (CRM 4.0 and CRM 2011 On-premise)
  • SOAP 2006 (CRM 3.0)

Note: The WebAPI service endpoint is made available since our v11.0 release. The WebAPI option has quite some limitations and restrictions as of Microsoft Dynamics 365 Customer Engagement v9.1 release (including Microsoft Dynamics CRM 2016). Those limitations and restrictions have been mentioned in the software UI which can be found by the "i" icon next to the Service Endpoint option. Please make sure you read and understand those limitations before you decide to use the option. 

Note: You may change the Service Endpoint option after a connection manager has been saved. However when doing so, it might happen that you have just switched to a service endpoint that uses an entirely different metadata system, which would consequently break all components that reference the connection manager. If this ever happens, you need to make sure that you will update all referenced SSIS data flow components by using its Refresh CRM Metadata button. The following are two important situations that you should take into consideration when making changes to this option.

  • WebAPI and SOAP2011 use the same metadata system, they are technically 100% compatible. No component refresh is required if you ever switch between the two options.
  • For CRM 2011 on-premise server, you could potentially use either SOAP2011 or SOAP2007 service endpoint, however these two service endpoints use two different metadata systems - so a component refresh is required if a change of the Service Endpoint has ever occurred.

Note: When using the SOAP2011 service endpoint to work with Dynamics 365 CE online (or CDS), you need to work with at least SSIS 2012 or above. SSIS 2008 (or lower) is not supported when working with Dynamics 365 CE online (or CDS) if the SOAP2011 service endpoint is used. However this limitation does not apply to the WebAPI service endpoint.

Authentication Type

The Authentication Type option allows you to specify the deployment type of your CRM server and what authentication is used. There are four options available.

  • Active Directory (On-Premise)
  • Federation (IFD, On-Premise or Partner-hosted)
  • Online Federation (Office 365 CRM Online - Office 365)
  • OAuth (Dynamics 365 Online or On-Premise)

Note: There used to be a fifth option called LiveId which is no longer available as Microsoft has officially retired it at around year 2016.

Note: The OAuth option is made available since our v10.0 release, it requires a selection of an OAuth Type option (discussed below). We generally recommend this option whenever possible, particularly if you are connecting to a CDS or Dynamics 365 CE online instance, as it offers better security management. In addition, when the Certificate or Client Credentials option (discussed below) is used, the application user created in your Dynamics 365 application does not consume a user license, which can save some licensing cost.

Note: For CDS connection, only the Active Directory and OAuth options are available, as the other options are not applicable.

OAuth Type (since v10.0)

The OAuth Type option allows you to specify the OAuth type when you use OAuth (Dynamics 365 Online or On-Premise) authentication. There are three options available.

  • Password
  • Certificate
  • Client Credentials (Server to server authentication)
CRM Discovery Server

The CRM Discovery Server option allows you to specify the CRM discovery server URL which you can use to discover your CDS/CRM organizations and their web service URLs. The CRM discovery server can be found in the CRM system from Settings | Customizations | Developer Resources after you have logged in to your CRM application.

Note: This option is not available if you use either the Certificate or Client Credentials OAuth connection, as CDS/CRM organization discovery is not available for such authentication scenario.

Please ensure to enter the full URL of your CRM discovery server, the path after the server name is not needed.  

When working with a Dynamics 365/CRM on-premise or IFD (Internet Facing deployment) environment, the URL should typically be in one of the following formats, depending on your CRM server's setup. 

  • http://CrmServerName/
  • https://CrmServerName/
  • http://CrmServerName:PortNumber/
  • https://CrmServerName:PortNumber/

When working with CDS/Dynamics 365 CE/CRM online, you should select the discovery server URL based on your region using the dropdown list offered since our v5.2 release. We generally try to keep the list as updated as it can be if you are using the latest version of our software releases. If it ever happens that there is a new region that Microsoft has just recently added which has not yet made into our official release, you can enter the discovery server URL manually. It is important to note that the URL should be in the format of https://disco.crm<region-id>.dynamics.com/, not your tenant URL such as https://myorganame.crm<region-id>.dynamics.com/. The following is a sample list of CRM discovery servers that you can enter in the CRM connection manager for Dynamics 365 (The list below is for reference only, we don't update the list as frequently as we do with our software). 

Location CDS/Dynamics 365 CE/CRM Online Discovery Server URL
North America https://disco.crm.dynamics.com/
North America 2 https://disco.crm9.dynamics.com/
Europe, Middle East and Africa (EMEA) https://disco.crm4.dynamics.com/
Asia Pacific Area (APAC) https://disco.crm5.dynamics.com/
South America https://disco.crm2.dynamics.com/
Oceania https://disco.crm6.dynamics.com/
Canada https://disco.crm3.dynamics.com/
India https://disco.crm8.dynamics.com/
Japan https://disco.crm7.dynamics.com/
United Kingdom https://disco.crm11.dynamics.com/
German https://disco.crm.microsoftdynamics.de/
Use Integrated Authentication

This option is only available if you have chosen "Active Directory (On-Premise)" as Authentication Type. When chosen, you will be connecting to the Dynamics 365 CE/CRM server using the currently logged in user account (essentially yourself if you are running the software interactively, or the service account / proxy account for scheduled job execution). Using this option, you have the flexibility of not saving your login credential in the SSIS package, which might help simplify the deployment process of your SSIS packages. 

Authorization Server (URL)

This option is introduced in our v11.0 release, and it is only available when OAuth is used. When connecting to Dynamics 365 CE/CRM online, we use https://login.microsoftonline.com/ as the default authorization server, which you don't generally need to make a change unless there is a specific reason in the case that your CDS/CRM online instance is using some special authentication flow. This is more often used for Dynamics 365 on-premise installation with a particular authorization server. To enable the option for editing, you must first click on the "Use a custom URL" checkbox next to the option.

The custom Authorization Server URL can be provided in the following format.

  • https://servername - when the value is provided this way, our software will automatically append common/oauth2/token after the server URL.
  • https://servername/<TenantId>/oauth2/token - when the value is provided this way with /oauth2/token at the end, our software will authenticate against the URL as it is. This is useful for the case when working with GCC or GCC high environment, you authorization URL is likely to be https://login.microsoftonline.us/<TenantId>/oauth2/token. You can find out your talent ID by logging into your Azure Portal.
User Name

The User Name option allows you to specify the user account that you want to use when connecting to your Dynamics 365 CE/CRM server. Depending on how the connection is used, the user account needs to have proper privileges in your CDS/CRM system . 

Password

The Password option allows you to specify the password for the above user account in order to login to your CDS/CRM server.

Note: The Password is not included in the CRM connection manager's ConnectionString property by default. This is done by design for security reasons. When you need to parameterize the connection manager for runtime execution, there are two options available in providing customized login credentials (including the Password). The first option is to parameterize the ConnectionString property and Password separately. The second option is to include the Password in the ConnectionString property, so you would just parameterize the entire ConnectionString with password included. The format would be adding ;Password=myPassword at end of the ConnectionString (or anywhere in the middle as long as the semicolon is inserted in the correct place). 

Service Resource (since v11.0)

The Service Resource option is only available when the the "Use a custom URL" is chosen, it allows you to specify the service resource that our software is accessing.

The Service Resource should typically be your tenant URL such as https://mytenant.crmx.dynamics.com/ when x represents your CDS or Dynamics region. For instance, if you are US based, it would be https://mytenant.crm.dynamics.com/, or https://mytenant.crm4.dynamics.com/ for EMEA, and so on. Note that it typically requires the trailing slash character, but you can always have a try of your tenant URL without the trailing slash. 

Domain

The Domain option is used to specify the active directory domain of the CRM user account. This option is only available when the Authentication Type is "Active Directory (On-Premise)". 

Client App Id

The Client App Id option is be available when the OAuth (Dynamics 365 Online or On-Premise) Authentication Type is selected, and it allows you to specify the ID (in GUID format) of the Azure Active Directory (Azure AD or AAD) application you have created for application authentication.

To create an application in Azure Active Directory (AAD):

  1. Log in Azure Portal
  2. Navigate to Azure Active Directory | App registrations | New registration
  3. Give a name to the application and choose an appropriate application type using the Redirect URI option on the page. 
    • Password: Public client (mobile & desktop), or Web
    • Certificate: Web
    • Client Credentials (server to server authentication): Web

In order to work with Certificate and Client Credentials (server to server authentication) OAuth Type, it is also required to have an Application User created in your Dynamics 365 CE/CRM instance. To create an Application User:

  1. Log in Dynamics 365/CRM
  2. Navigate to Settings | Security | Users and switch to Application Users view. Please do make sure you are in this view otherwise the user you create would not be an App User.
  3. Click New and fill out the form to create an Application User. Please do make sure the user type is "USER: APPLICATION USER", the Application ID is the Client App Id that we've created in the previous step.
Certificate Thumbprint

The Certificate Thumbprint option is only available when the Certificate OAuth Type has been chosen, it allows you to specify the thumbprint of the certificate created for the application registered in Microsoft Azure Active Directory (AAD).

Before generating a thumbprint in Microsoft Azure Active Directory (AAD), you would need to create a Public/Private Key Pair:

  1. Download and install OpenSSL for Windows. Note that you may need to restart your machine after installation
  2. Launch Windows Command Prompt as an administrator using the "Run as administrator" option
  3. In the command prompt window, navigate to the bin folder of the installation location of OpenSSL, e.g. "cd C:\OpenSSL-Win32\bin"
  4. Execute the following commands. Note that the third command will prompt you to enter a password. Two files will be generated in this step:
    • publickey.cer: this is the Public Key file that is going to be uploaded to Microsoft Azure Active Directory (AAD).
    • public_privatekey.pfx: this is the Private Key file that is going to be installed on the machine where CRM Connection Manager is used.

    openssl genrsa -out privatekey.pem 2048
    openssl req -new -x509 -key privatekey.pem -out publickey.cer -days 1825
    openssl pkcs12 -export -out public_privatekey.pfx -inkey privatekey.pem -in publickey.cer

To generate a thumbprint for a specific Web App / API application in Azure Active Directory:

  1. Log in Azure Portal
  2. Navigate to Azure Active Directory | App registrations and select the application
  3. Click Certificates & secrets | Upload certificate
  4. Specify the location of Public Key file (publickey.cer) and click Save to display the Thumbprint.

Once you get thumbprint from Azure Active Directory, please be sure to install the Private Key file (public_privatekey.pfx) on the machine where CRM Connection Manager is used.

Note: This option will only be available to Certificate OAuth Type.

Client Secret

The Client Secret option allows you to specify the client secret once you have selected or created an application in Microsoft Azure Active Directory (AAD).

To create a client secret for a specific Web App / API application:

  1. Log in Azure Portal
  2. Navigate to Azure Active Directory | App registrations and select the application
  3. Click Certificates & secrets
  4. Click the New client secret button and follow the instructions on screen to enter a description of the key and specify whether the key should expire
  5. Click Add to save the Client Secret, and make sure to take a note of the client secret right away

Note: This option will only be available to Client Credentials (server to server authentication) OAuth Type.

Authorize Button

By clicking the Authorize button, it will pops up a Application Access Authorization window to help you authorize the application to access your Dynamics 365/CRM data. All you need to do is to provide the Redirect URL depending on your Azure Application's application type:

  • Native application: put the Redirect URI of the application to the Redirect URL field.
  • Web app / API application: put the Sign-on URL of the application to the Redirect URL field.

Two types of authorizations are supported:

  • Authorize in App
  • Authorize in Browser

Note: This option will only be available to OAuth (Dynamics 365 Online or On-Premise) Authentication Type.

Organization

After the CRM server location and login credentials have been provided, you can click the drop down button of the Organization option to show a list of available organizations that the user has access to. Select the organization that is intended to be used. 

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully login to the CRM server.

If you happen to run into the following error message when testing the connection, it is most likely that the provided CRM organization does not exist in the target CRM server.

Metadata contains a reference that cannot be resolved: 'http://xrmsvr/CrmOrganizationName/XRMServices/2011/Organization.svc?wsdl'. (System.ServiceModel)
ADDITIONAL INFORMATION:
The remote server returned an error: (404) Not Found. (System)

Connection error

Proxy Server page

The Proxy Server page of CRM Connection Manager allows you to specify how you want to configure the proxy server.

CRM Connection Manager

Proxy Mode (since v8.0)

Proxy Mode option allows to specify how you want to configure the proxy server setting. There are three options available.

  • No Proxy
  • Auto-detect (Using system configured proxy)
  • Manual
Proxy Server

Using the Proxy Server option, you can provide a proxy server to connect to the CRM server.

Port

The Port option allows you to specify port number of the proxy server for the connection.

Username (Proxy Server Authentication)

The Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

The Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

Note: Proxy Password is not included in the CRM connection manager's ConnectionString property by default. This is done by design for security reasons. However you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Advanced Settings page

The Advanced Settings page of CRM Connection Manager allows you to specify some advanced settings of the connection. 

CRM Connection Manager

Timeout (secs)

The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 60 seconds.

CRM Server URL (since v5.1)

The CRM Server URL is the actual URL that is utilized by the connection manager to make service calls to CRM. This field should be prepopulated for you. In special cases where you want to specify the CRM Server URL, you can change the value by first unlocking the field using the lock/unlock button next to it.

Home Realm Uri (since v4.1)

Specify the URI of the cross realm STS metadata endpoint. It should typically be a URL starting with https. This option is usually optional, and it may be required for certain deployment. In the case that this option is required, it should typically be in the following format.

https://adfs-server-name.mycompany.com/adfs/services/trust/mex
Retry on Intermittent Errors (since v5.1)

This is an option designed to help recover from possible intermittent outages or disruption of service so the integration does not have to be stopped because of such temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: Although we have carefully designed this feature so that such retries should only happen when it is deemed to be safe to do so. However, in some extreme occasions, such retried service calls could result in the creation of duplicate data.

Note: between v3.0 and v5.0 SR-1 (inclusive), the retry implementation was enabled by default, and there is no option to turn it off until v5.1 release.

Ignore Certificate Errors (since v7.2)

This option can be used to ignore those SSL certifcate errors when connecting to CRM server.

Warning: Enabling "Ignore Certificate Errors" option is generally NOT recommended, particularly for production instance. Unless there is a strong reason to believe the connection is secure - such as the network communication is only happening in an internal infrastructure, this option should be unchecked for best security.

Note: When this option is enabled, it applies to all HTTP-based SSL connections in the same job process, it is not just limited to Dynamics CRM connections.

More Info page

The More Info page contains basic information about the toolkit. In this page, you can find the version information of the toolkit.

CRM Connection Manager