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:
- Install Web Server IIS
- Create and Bind a Self-Signed SSL Certificate
- 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.
Server manager
From the available options, click to select Add Roles and Features.
Add roles and features
Review the Before you begin information, then click Next to continue.
Before you begin
Accept the default selection for Select installation type, then click Next to continue.
Select installation type
Confirm that the correct server is selected for Select destination server, then click Next to continue.
Specify the appropriate server
Click to select the checkbox next to Web Server (IIS), then click Next.
Install IIS
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.
Add features
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.
Enable Windows 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.
Creating 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.
Setting bindings
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.
Setting up 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.
Install OData services
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 string
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
Configuring the OData service
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.
Login as an administrator
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.
Setting up reporting tools
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
Adding 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
URL strings
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.
Setting up security
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.
Adding roles to users
Once these steps are completed, your Dynamics GP OData service is ready to go. If you have questions, please feel free to contact sa.global at 312-757-5499 or email [email protected]. Also, please check back for our post next week to help walk-through how to use the newly created OData service with Power BI!