Create a Dynamics GP Jet Remote Data Source - Admin Guide
Expert
Overview
The Jet Remote Data Service (JRDS) data source presents a three-tier architectural design with a service component that is hosted within Internet Information Services (IIS). It provides a fast, secure connection method for environments where Excel is installed locally and the Dynamics GP system is hosted on a different domain
Table of Contents
Requirements | Terminology | JRDS Admin Shell | Installation | Configuration |
This article is designed for use by system administrators wanting to implement the Jet Remote Data Service data source for use with Dynamics GP. If you are a Jet Reports * user * and want to configure the Jet Excel add-in to use an existing Jet Remote Data Service data source that connects with your Dynamics GP, contact your system administrator for details and then follow the instructions listed here
Requirements
Host Server Requirements
- Using Dynamics GP 2013 or later
- SQL Server and its corresponding SQL Server client must be installed
- Internet Information Services (IIS) with an SSL/TLS certificate
- PowerShell 4.0 (or higher)
- Microsoft .NET Framework 3.5 enabled
- The name of the host server must be exposed through DNS if users will access it via a URL
- The service must have access to a SQL account or Windows Login which has ‘read’ access to the GP database.
The recommended best practice is to set up a single new user account with read access to the database. Then, use the Jet Administration Console to create a data source using the credentials for this new account, and share that data source with any Jet users who will need to use the data source. In this way, Jet users have access to the data without having to know the credentials associated with the new user account.
End User (client) Requirements
- The Jet Excel add-in (versions 2015 Update 1 or higher) must be installed on the local computer.
To connect to the service, end users will need to know...
- The URL or IP address of the Host Server
- The Port configured for use with the JRDS
- The Instance Name of the Service Instance
- Their GP username, domain and password
Terminology
-
" Jet Remote Data Service " vs " JRDS Data Source "
-
Jet Remote Data Service
The JRDS consists of an Application Container and one or more Service Instances
In mult-tenant environments, each hosting client must have a distinct service instance with its own data source settings. This ensures that no hosting client can access data from another hosting client.
-
The Application Container
The Application Container is the term used to refer to the IIS website that will host the Service Instance (s). The Application Container is associated with a port during installation.
-
Service Instances
The Application Container must include one or more Service InstancesEach Service Instance must be uniquely named and provides access to a single GP data source.
In a multi-tenant environment all clients may connect to their data via the same port, but each client will connect to their own service instance.
Jet Remote Data Service Administration Shell
The Jet Remote Data Service is installed and configured using the Jet Remote Data Service Administration Shell , which is a PowerShell management module.
The MSI file can be used (with the Windows Installer for other installation tool) to install the Administration Shell into a “Jet Remote Data Service” directory in the appropriate “Program Files” directory.
Along with the Administration Shell, the MSI also installs the service assemblies and a shortcut in the Start Menu (to access the administration shell).
Click DOWNLOAD to download a zip file containing both the 32-bit and 64-bit installer:
The bit-level you install should match the bit-level of the version of the operating system (usually 64-bit for recent versions of WIndows Server).
There are two ways to initialize the management module. The first is to use the shortcut called “Jet Remote Data Service Administration Shell”. This must be run as administrator and will open an instance of Powershell.
Alternatively, the initialization script (JetRemoteDataServiceAdministrativeShell.ps1) can be found in the Management folder of the install directory and run manually via the PowerShell prompt running as administrator
If the download does not begin for the installer, please copy the link and paste it in a new browser window.
Installing a Jet Remote Data Service
The Jet Remote Data Service Administration Shell provides a set of PowerShell cmdlets (pronounced "command lets") which are used to create, configure and remove Application Container and Service Instances. Each of the available cmdlets and their common uses are listed below.
Installation Steps
-
New-JetRemoteDataService
This command creates the Application Container and must be called prior to calling other Jet cmdlets.
The parameter -Port is required and indicates the port that will be used for the website address binding.
This command must be called prior to calling New-JetGPRemoteDataServiceInstance
Example:
PS C:\> New-JetRemoteDataService -Port 443
This creates a new, empty website in Windows IIS that is named Jet Remote Data Service Container which serves as the container for service instances of the JRDS.
The port used will need to have an inbound Windows firewall rule in place. insightsoftware recommends that port 443 be used.
Supported Parameters
Parameter Description Type of Value Example Value or Description -Port The port to be used by the Jet Remote Data Service Numeric 443 The command does support other parameters. For additional details, you can use the PowerShell "get-help" cmdlet
Jet cmdlets also support the common parameters: Verbose, Debug, ErrorAction, ErrorVariable, WarningAction, WarningVariable, OutBuffer, and OutVariable. For more information, see about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216)
-
New-JetGPRemoteDataServiceInstance
This command creates a service instance of the JRDS. This command can be used multiple times to create multiple Service Instances. The New-JetRemoteDataService command must be called prior to running this command.
Example:
PS C:\> New-JetGPRemoteDataServiceInstance -InstanceName "Customer" –Server “Jet-Test” -WindowsAuthenticationType $false -UserID "JETSA" -Password "m!saP@$$w0rd!"
When a new service instance is created:
- An instance directory is created in the IIS website root directory that contains a symbolic link to the installed service assemblies and a “Web.config” file. This new directory is loaded into the Application Container as a virtual directory whose path is the name of the instance, and within that virtual directory the symbolic link is loaded as an application.
- The instance application will be given its own Application Pool within IIS. This results in an instance-specific IIS process being created for each service hosted in the Application Container.
A virtual directory in the Application Container is mapped to a distinct instance directory and the link to the service assemblies is located as an application. The figure above shows a virtual directory inside of the Application Container and the location of the actual directory in the file system. Within that instance directory, a symbolic link is pointing to the location of the service assemblies (which in this example is the default installation directory).
Parameters Supported
Parameter Description Type of Value Example Value or Description -InstanceName The name of the new Service Instance Text "Customer" -DynamicsDB The DYNAMICS database name. Text "XYZ-DYNAMICS" -WindowsAuthentication A value indicating whether or not to use Windows Authentication for connecting to the DynamicsDB database.
Boolean
$false
$true
-UserID The userID used to authenticate with the spcified DynamicsDB database.
This userID must have read permissions to the GP SQL database.
Text "Username" -Password The password used to authenticate the UserID with the DynamicsDB database.
Text "paSSw0rd" -Server The SQL server on which the DynamicsDB database resides
Text "Jet-Test"" (parameters in RED are required)
If WindowsAuthentication is set to $false then the credentials specified for UserID and Password will be used when the JRDS service connects to the DynamicsSB database. The credentials specified on the Jet Data Source settings will be used for authenticating the client with the JRDS.
If WindowsAuthentication is set to $true then the credentials specified in the Jet Data Source settings will be used for authentication both the client with the JRDS as well as the JRDS with the DynamicsDB.
Note:- All switches are proceeded by a dash ( - )
- There is no space between the dash and the switch name
- There is always a space between the switch and its value
- For boolean (True/False) switches, the value is in lowercase and preceded by the dollar symbol ( $ )
- Text (alpha-numeric) values are enclosed within double quote marks
- Numeric values have no special formatting
-
Set-JetGPRemoteDataServiceInstance
This command is used to modify the data source settings of an existing Service Instance. The only required parameter is -InstanceName which indicates the instance of the service whose settings are to be configured.
In this example, the setting -DynamicsDB is modified:
PS C:\> Set-JetGPRemoteDataServiceInstance -InstanceName "Customer" -DynamicsDB "XYZ-Dynamics"
Parameters Supported : See the list for the New-JetGPRemoteDataServiceInstance cmdlet
-
Remove-JetRemoteDataServiceInstance
This command is used to remove an instance of the JRDS. The only required parameter is -InstanceName which indicates the instance to remove from the Application Container.
Example Command:
PS C:\> Remove-JetRemoteDataServiceInstance -InstanceName "Customer"
-
Remove-JetRemoteDataService
This command is used to remove the Application ContainerNo parameters are necessary.
Example Command:
PS C:\> Remove-JetRemoteDataService
Configuring User and Group Access
By default, all users are authorized to access the JRDS. The full set of allowed and denied users can be viewed with the Get-JetRemoteDataServiceInstance command and noting the values shown for Authorized Users, Authorized Groups, Denied Users, and Denied Groups.
For details about how to configure JRDS access for specific users and/or groups, see the article Configuring user and group access to the JRDS
End-to-end Configuration of the Jet Remote Data Service
Using the tools discussed above, we’ll now look at a step-by-step walkthrough of setting up the Jet Remote Data Service and a data source connection to it in Jet Reports.
An important distinction to remember is the difference between:
- The JRDS instance configuration to the database, and
- The configuration in the Jet Excel add-in for connecting to the JRDS
Individuals in charge of configuring the JRDS in IIS will need to configure the JRDS instance's connection to the database. Jet users will then need to configure their connection to the JRDS
-
Creating and Configuring an Instance of the JRDS
Hosting providers will need to configure the following items. The recommended configuration is to use an AuthenticationType of SqlServer for the database. This makes it easier to manage security.
Here is an example of creating a new service instance for a customer named “JetReports” using port 443 on a server named “Jet-Test”:
PS C:\> New-JetRemoteDataService -Port 443
PS C:\>New-JetGPRemoteDataServiceInstance -InstanceName "Customer" -Server "Jet-Test" -UserId "JETSA" -Password "m!saP@$$w0rd! "
The bare minimum settings have been configured to make this data service usable.
-
Binding the Security Certificate
*** IMPORTANT MANUAL STEP ***
When the Application Container is created, no security certificate (e.g., SSL/TLS) is configured for it. In order to complete this step, Windows IIS Manager must be opened and the binding on the site entitled Jet Remote Data Service Container must be configured to use a security certificate, as shown below
It is important that a trusted certificate be used. Additionally, the site that the certificate was issued to must be the site used in the "Server" value when configuring your users' Jet data source settings. Failure to follow these guidelines may result in certificate warnings for your users and an inability to connect to the data source. See the certificate properties and data source settings shown below.
-
Configuring the Data Source Connection in the Jet Excel add-in
The first step in the Jet Excel add-in is to create a new data source of the type Dynamics GP with a connection type of Remote Data Service. Next, the authentication needs to be configured. The user can authenticate against the JRDS using specified Windows credentials.
To create a new data source connection to your GP database, start by selecting Settings > Data Source Settings from the Jet ribbon.
On the Data Source Settings window, click the Add button. When the Add Data Source dialog will appear, provide a unique name for our GP data source, select Dynamics GP for the Database Type , and Remote Data Service for the Connection Method.
It is expected that users will be given a username, domain, and password to use for authentication.
The connection information for the instance of the JRDS must be configured:
The dropdown to select the company will populate when opened if the settings are correct.
Once the Data Source Settings are correct, you can test them by clicking the Test Connection button at the top of the Data Source Settings window and should see the Test Connection Succeeded message. If there are any issues, the message should help you in troubleshooting.
Important Note
When upgrading versions of Jet to a much newer release, ensure that both the Jet Remote Data Service version installed on the server and the client versions are on the same version to reduce potential issues.