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 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

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 that Online Federation option is only available since v5.2 release. If you use any prior versions, you should choose Federated option when working with an Office 365 (OSDP) CRM Online instance.

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 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. 

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

For instance, the following is a sample list of CRM discovery servers that you can enter in the CRM connection manager if you are using CRM online or Office 365 (The list below is for reference only, and it may not represent the most updated information). 

Location Office 365 CRM Online
Discovery Server URL
Legacy CRM Online
Discovery Server URL (Live ID)
North America https://disco.crm.dynamics.com/ https://dev.crm.dynamics.com/
EMEA https://disco.crm4.dynamics.com/ https://dev.crm4.dynamics.com/
APAC https://disco.crm5.dynamics.com/ https://dev.crm5.dynamics.com/
South America https://disco.crm2.dynamics.com/ https://dev.crm2.dynamics.com/

Note that since v5.2, we offer a drop-down list which allows you to pick the URL from the list based on your region, so you no longer need to type in the above URLs.

Similarly, for On-Premise or IFD deployments, 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/
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 CRM server. We currently support the following options:

  • SOAP 2011 (CRM 2016, 2015, 2013, 2011)
  • SOAP 2007 (CRM 4.0 and CRM 2011 On-premise)
  • SOAP 2006 (CRM 3.0)

Depending on the version of your CRM server, you can choose different service endpoints. For CRM 2011 On-Premise Server, you can use either the SOAP 2011 endpoint or the SOAP 2007 endpoint. The SOAP 2011 endpoint is generally recommended as we have better support for activity party (partylist) fields. SOAP 2011 is also the latest service interface, which is considered to have a better upgrade path in the future.

SDK Client Version (deprecated since v6.0)

The SDK Client Version option allows you to specify the SDK version that you use to talk to the CRM Server. This option is only available when the SOAP 2011 service endpoint is used. Currently there is only one option available.

  • 6.0

The option is only necessary (when available) if you need to work with entityimage field, which is a new feature introduced since CRM 2013. Since our v6.0 release of the toolkit, we always send in a SDK Client Version in order to work with the latest CRM metadata.

Use Integrated Authentication

This option is only available if you have chosen "Active Directory (On-Premise)" as Authentication Type. 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. 

User Name

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

Password

The Password option allows you to specify the password for the above user account in order to login to your 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. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be Password=myPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString. 

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)". 

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. 

Client App Id

The Client App Id option allows you to specify the GUID value that identifies a client application in Microsoft Azure Active Directory (AAD). Note that you need to register your application with AAD via the Azure portal in order to generate your Client App Id.

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

  1. Log in Azure Portal
  2. Navigate to Azure Active Directory | App registrations | New application registration
  3. Create new application according to the OAuth Type you are using:
    • Password: Native application
    • Certificate: Web app / API application
    • Client Credentials (server to server authentication): Web app / API application

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 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 CRM would not allow you to create a new 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.

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

Certificate Thumbprint

The Certificate Thumbprint option allows you to specify the thumbprint once you have selected or created an application 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 1024
    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 Web App / API application
  3. Click Settings | Keys | Upload Public Key
  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 Web App / API application
  3. Click Settings | Keys and fill out the required fields
  4. Click Save to display the Client Secret

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.

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