Monday, June 8, 2015

Dynamics AX Setup for OData Integration with Salesforce

Microsoft Dynamics AX
In an attempt to access Microsoft Dynamics AX 2012 data in Salesforce through Lightning Connect, much of my effort went towards getting the OData service in AX setup correctly.  Here is an overview of my steps to get the environment up and running.

Environment Setup

Obtaining a Dynamic AX demo environment was easy.  With Microsoft Lifecycle Services, it was just a few steps to be up and running.
  1. Log into Life Cycle Services
  2. Create a project
  3. Connect to an Azure subscription (I used a 30 day free trial)
  4. Deploy an Dynamics AX demo environment on Azure
  5. Log in via Remote Desktop and open the Dynamic AX Client
The AX demo environment is setup with a demo organization, including out of the box setting, data, etc.and is ready to go.


With Dyanmics AX, OData service is available out of the box with access to the queries setup in AX.  It is accessible via http://<host>.<domain>:8101/.   This provides a list of the available queries that can be called, by adding the name to the end of the URL.  For example, get a list of all US states via https://<host>.<domain>:8101/DynamicsAX/Services/ODataQueryService/ListofUSStates.

Dynamics AX OData Query Service Tables

You are required to authenticate to the service first to ensure you are authorized to access the specified resources.  Now the astute reader may notice the issue we will have with Salesforce.  As we are authenticating to the AX OData Service, we must specify a secure URL when setting up our External Data Source within Salesforce

ENABLING HTTPS for Dynamics AX Odata

As you will see the actual changes needed to enable HTTPS are not that extensive.  However, I could not find a clear set of instructions, so good old fashion research and trial and error lead to to the following setup tasks.

First some background, the AX OData Services is a Windows Communication Foundation (WCF) app that runs as a service - the Ax32Serv.exe.  While some WCF apps run inside of  Internet Information Services (IIS), this one is stand alone.  As such, the setup is not done inside of IIS.

SSL Certificate

A SSL certificate is needed in order to communicate on HTTPS.  If you have a certificate that matches the final domain you will deploy the OData Service to, use it now.  If not, you can create a self-signed certificate.  There instructions are for IIS7 and a quick web search will bring up other options.  When attempting to configure Lightning Connect in Salesforce, a self-signed certificate will not be trusted and the connection will not work, but it gets us through the first step.

Port Binding

Once you have your SSL cert, we need to configure port 8101 to use it.  Full details on how to do this can be found in this article - Configure a Port with an SSL Certificate.  The summary is:

  • Check the port configuration from command line 
    • netsh http show sslcert ipport=
    • No results indicates nothing is bound 
  • Get the SSL certificate's thumbprint from MMC
  • Bind the SSL cert to the port from command line
    • netsh http add sslcert ipport= certhash=<tumbprint> appid={00112233-4455-6677-8899-AABBCCDDEEFF}
  • Use the check command again to verify the bound cert

Setup URL Reservation

A reservation is needed to allow the WCF service to listen on the assigned portion of the URL.  Full details can be found in this article - Configuring HTTP and HTTPS.  The summary is:
  • Check the existing reservation.  There is likely a HTTP reservation already in place
    • netsh http show urlacl url=https://+:8101/DynamicsAx/Services/
    • Be sure to include the trailing /
    • No results means there is not a reservations.  
  • Delete any existing reservation
    • netsh http delete urlacl url=https://+:8101/DynamicsAx/Services/
  • Add a new reservation with HTTPS
    • netsh http add urlacl url=https://+:8101/DynamicsAx/Services/ user=CONTOSO\admin
      • Be sure to change the user to the user that runs the service
  • Use the check command to verify the reservation

Ax32Serv.exe Config File Changes

Now it is time to update the config file for the AX Service, which handles the WCF OData service.  The Ax32Serv.exe.config file can be found in C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin (change to your install drive).  You may want to create a backup before you change.  The Service Endpoint, Service Behavior and Binding Method all need to be changed.

Service Endpoint
  • Find the <services> section and look for the service name="Microsoft.Dynamics.AX.Framework.Services.ODataQueryService.ODataQueryService"
  • Update the address of the endpoint to be https instead of http
Service Behavior
  • The service definition references a behaviorConfiguration="ODataQueryServiceBehavior"
  • Locate this behavior name in the <behaviors> section
  • Change httpGetEnabled to httpsGetEnabled
  • Change httpGetURL to httpsGetURL and modify the address to be https instead of http
Binding Method
  • The service definition references a binding="webHttpBinding" and binding configuration="ODataQueryServiceBinding"
  • Location this binding in the <bindings> section
  • Change the mode in <security> from "TransportCredenialOnly" to "Transport"
  • Change the clientCredentialType from "Windows" to "Basic"
  • I removed the proxyCredentialType="Windows" attribute as well
My updated config file:

Updated Config File with OData on HTTPS

Save your updated config file and restart the Microsoft Dynamics AX Object Server 6.3 service from Services in MMC.

Firewall and Azure Endpoint Management 

Although I did not run into an restrictions with Windows Firewall in my Dynamics AX demo environment, keep in mind that access to port 8101 will be needed for this solution to work.  As Dynamics AX is deployed to a virtual machine within Azure, you need to setup an endpoint for the VM to communicate with the rest of the world.  You can review all the details of Setting Up Endpoints for a Virtual Machine.  Essentially, you need to go to Virtual Machines in Azure Management Portal, click Endpoints, Add a new endpoint, and link a public port of 8101 to the private port of 8101 with the TCP protocol.

Verifying Locally

You can now browse locally to the OData service on HTTPS - https://localhost:8101/DynamicsAx/Services/ODataQueryService/  You will likely receive a certificate error if using a self-signed cert and be prompted for a login.  You will see a list of "tables" that you can then query for results.  

You can also verify by pulling the data into Excel on your own machine.  You will need to use the URL from Azure - 
https://<instance> Again, you will need to click through the certificate error and supply credentials.  You can also add the self-signed certificate to your local trusted certificate authority certificate store.

Open a new Excel workbook, go to Data -> From Other Sources -> From OData Data Feed.  Supply the URL, credentials and click next.  Pick your table, I used ListofUSStates to keep it simple.  Finish to save the data source, select a location in the sheet and your Dynamics AX data is now in Excel.  Here is an example list of states from Dynamics pulled in Excel.

Microsoft Dynamic AX Data in Excel through OData Query

Lightning Connect Preparation

In order to setup the OData Connection in Salesforce, we need to have a trusted certificate.  As we supply credentials to connect to Dynamics, the URL must be HTTPS, with a valid cert.  Since we do not own * we cannot purchase a certificate for it.  Instead, we must setup a custom domain with Azure Cloud Services and secure an application in Azure Cloud Services. There are two knowledge base entries with the details of each of these steps - Associating a custom domain and securing communication with Microsoft Azure.  Use a custom domain that you own and have a valid SSL certificate for.

Now with this final, external URL and valid SSL certificate, we are ready to try and connect from Salesforce, which I will discuss in another post.  To add a little foreshadowing, things did not go as planned.

1 comment:

  1. Finally a proper guide for OData integration, thank you!