Configuring the OData Service for Dynamics GP

 In Blog

The ODATA Service for Dynamics GP opens up secure access to your GP data for external applications by leveraging the existing GP security module.  Once deployed, GP Administrators will be able to grant users access to SQL objects using the native ODATA security role IDs within User Security Setup.  GP users granted data access will be afforded the opportunity to create powerful reports and integrations through tools such as Power BI and Excel.   In this series of posts, we will go step-by-step through the process of enabling ODATA.  

 In order to follow along, you will need: 

  • Dynamics GP 2016 or later 
  • Admin access to the GP server 

The process has a three main steps, and should take about 2 hours to complete: 

  1. Install Web Server IIS 
  2. Create and Bind a Self-Signed SSL Certificate 
  3. Install and Configure the Dynamics GP ODATA Service

Installing IIS for Dynamics GP

In this installment in our series on configuring the ODATA Service for Dynamics GP, we will cover how to install Web Server IIS. IIS stands for Internet Information Services, which basically provides an entry point to the Dynamics GP web client.

Here’s how to configure it:

Open Server Manager, then click Manage at the upper corner.

windows server manager dashboard

From the available options, click to select Add Roles and Features.

windows server add roles and features

Review the Before you begin information, then click Next to continue.

windows server add roles and features wizard

Accept the default selection for Select installation type, then click Next to continue.

windows server selecting a role based installation

Confirm that the correct server is selected for Select destination server, then click Next to continue.

windows server select a destination server

Click to select the checkbox next to Web Server (IIS), then click Next.

windows server select server roles

In the Add features that are required for Web Server (IIS) dialog box, click Add Features to continue.  Once redirected back to Select server roles, click Next to continue.  Click to expand .NET Framework 4.6 and WCF Services, then click make the checkbox next to HTTP Activation.

selecting features in windows server

In the Add features that are required for HTTP Activation dialog box, click Add Features to continue.  Once redirected to Select features, click Previous to be direct back to Select role services.  Click to expand Web Server (IIS), Web Server, and Security then click to mark the checkbox next to Windows Authentication. Click Next to continue.

windows server adding web server and authentication

Reconfirm the selections in Select features, then click Next to continue.  Confirm the objects to be installed, then click Install to initiate the install procedures.  Once the installation has completed, click Close to complete the install process.

Next, we’ll cover How to Create and Bind a Self-Signed SSL Certificate.

HOW TO CREATE AND BIND A SELF-SIGNED SSL CERTIFICATE

Having this SSL certificate allows the secure encryption of the traffic between your GP server and the services consuming the data.  Since we’ve already installed IIS, here’s how we set it up.

Launch Internet Information Services (IIS) Manager, then click to select the server within the connections pane on the left side. Double click Server Certificates.

IIS server certificates

Click Create Self-Signed Certificate in the Actions pane on the right side, enter a name for the certificate and leave Personal as the certificate store. Click OK to continue.

IIS create a self signed certificate

Click to select Sites in the connections pane, then click to select Default Web Site. Click Bindings in the Actions pane then click to select port 443.  Click Add if port 443 does not exist, otherwise, click Edit.

adding https binding

Ensure https is selected as the Type, then click to select your newly created certificate in the SSL certificate drop down list. Click OK to complete the binding.

assigning a certificate to https

Close Internet Information Services (IIS) Manager.  Finally, we’ll setup the GP OData service.

INSTALL AND CONFIGURE THE DYNAMICS GP ODATA SERVICE

Let’s dive in:

Using the GP install media, click to select GP OData Services to start the install process.

Installing the GP OData service

Click to accept the terms and license agreement, click Next to continue.  Enter the server & SQL instance name (if named instance is in use), then enter the GP system database.  Either accept the use of windows authentication to access the SQL instance or provide the proper SQL credentials to provide access.  Click Next to continue.

SQL connection information

Accept the default of port 443 for the OData Service, then click to select your SSL certificate in the available dropdown list. Enter the domain, username, and password of the domain account to own the newly created OData Service.  Click Next to continue.

NOTE – Be sure to make note of the Host Name as this will be used during the configuration of OData within GP

Specifying OData settings

Click Install to initiate the installation procedures

Click Exit once the installation process has completed

CONFIGURING ODATA ACCESS WITHIN GP

Now that we have the OData Service installed we need to configure OData Access within GP.

Here’s how:

Launch GP and login using either SA or another GP user with proper rights to configure the ODATA features.

Logging into Dynamics GP

Open Reporting Tools Setup by navigating to Tools -> Setup -> System -> Reporting Tools Setup, then click to select the OData Enter the Host Name obtained during the OData Services install into the OData Service URL field.  Click OK to continue.

Dynamics GP reporting tools setup

Open Data Sources by navigating to Tools -> Setup -> System -> OData -> Data Sources, then click to select the table / view objects to be made available via the OData connection. Click OK to continue.

NOTE – use the Add Objects function from the Actions section to create data sources for newly created SQL views

GP data sources

Open Publish OData by navigating to Tools -> Setup -> System -> OData -> Publish OData, then click to select the publish checkbox next to those data objects to be made available via through the OData connection. Click OK to continue.

NOTE – Please be sure to make note of the OData URL string as shown in Publish Odata.  This URL string will be used to establish the data connection from Power BI to your GP data

Publishing OData sources

Open User Security to grant the GP user access to the recently published OData objects. Click Save to apply the security changes

NOTE – The canned / native GP security roles which grant access to the OData objects all start with “OD_”.  You must append or create a new security task in order to provide access to new SQL views as they will not be included in the roles which came with the system.

Dynamics GP user security setup

Open GP User Setup to assign an Active Directory account to the GP user with access to the recently published OData objects.

NOTE – It is required to assign an Active Directory account as this will be the credential used to access the GP OData objects from within Power BI.

GP user setup

Once these steps are completed, your Dynamics GP OData service is ready to go.

If you have questions, please feel free to contact Dynamic Consulting at 312-757-5499 or email info@dynamicconsulting.com.  Also, please check back for our post next week to help walk-through how to use the newly created OData service with Power BI!

Showing 2 comments
pingbacks / trackbacks

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Have a question? Want more information? Let us know!

Not readable? Change text. captcha txt