Create a Data Warehouse Remote Data Source - Admin Guide

What is the Jet Remote Data Service? 

Requirements

Terminology

1. "The Jet Remote Data Service" vs "JRDS Data Source"

   

2. The Jet Remote Data Service

   The JRDS consists of an Application Container and one or more Service Instances.

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

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

   

4. Service Instances

   The Application Container must include one or more Service Instances.  Each Service Instance must be uniquely named and provides access to a single Jet Data Warehouse data source.

The 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 as shown below in Figure 3.

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 .

Installing a Jet Remote Data Service 

The Jet Remote Data Service Administration Shell provides a set of PowerShell cmdlets 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

1. New-JetRemoteDataService

   This command creates the Application Container and must be called prior to calling other Jet cmdlets.

   The parameter ‘-Port’ is required, which indicates the port that will be used for the website address binding.

   This command must be called prior to calling New-JetWarehouseRemoteDataServiceInstance

   Example Command

   New-JetRemoteDataService -Port 443

   This port will need to have an inbound Windows firewall rule in place.  Jet Reports recommends that port 443 be used.

   This creates a new, empty website in IIS that is named “Jet Remote Data Service Container” which serves as the container for service instances of the JRDS.

    

   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)

2. New-JetWarehouseRemoteDataServiceInstance

   This command creates a Jet Data Warehouse 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 Command (Windows Authentication)

   New-JetWarehouseRemoteDataServiceInstance -InstanceName "Customer" –WindowsAuthentication $true

    

   Example Command (SQL Authentication)

   New-JetWarehouseRemoteDataServiceInstance -InstanceName "Customer" –WindowsAuthentication $false
   –UserId “sa” –Password “saPassword”

    

   When a new service instance is created:

   1. 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.
   2. 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:

   These parameters can be used with the New-JetWarehouseRemoteDataServiceInstance or the Set-JetWarehouseRemoteDataServiceInstance commands:

    

   Parameter	Description	Type of Value	Example Values or Description
   -InstanceName	The name of the new Service Instance	Text	"Customer"
   -WindowsAuthentication	A value indicating whether to use Windows Authentication for connecting to the Jet Data Warehouse database	Boolean	$False $True
   -UserID	The UserID used to authenticate with the specified Jet Data Warehouse database This UserID must have read permissions to the Jet Data Warehouse database	Text	"UserName"
   -Password	The password used to authenticate the UserID with the Jet Data Warehouse database	Text	"PaSSw0rd"
   -Server	The SQL server on which the Jet Data Warehouse database resides	Text	"Jet-Test"

   (parameters in RED are required)

   If WindowsAuthentication is set to $false then the Windows credentials specified inside Jet’s Data Source Settings will be used for connecting to the IIS instance hosting the JRDS, and credentials stored on the JRDS will be used for connecting to the warehouse database.

   If WindowsAuthentication is set to $true then the Windows credentials specified inside Jet’s Data Source Settings will be used for connecting to the IIS instance hosting the JRDS, as well as for connecting to the warehouse database.

   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

3. Set-JetWarehouseRemoteDataServiceInstance

   This command is used to configure the data source settings of an instance of the JRDS. The only required parameter is ‘-InstanceName’ which indicates the instance of the service whose settings are to be configured. In this example, the setting ‘-WindowsAuthentication’ is modified.

   Example Command

   Set-JetWarehouseRemoteDataServiceInstance -InstanceName "Customer" -WindowsAuthentication $false

   Parameters Supported:  See the list for the New-JetWarehouseRemoteDataServiceInstance cmdlet

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

   Remove-JetRemoteDataServiceInstance -InstanceName "Customer"

5. Remove-JetRemoteDataService

   This command is used to remove the Application Container. No parameters are necessary.

   Example Command

   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 the Jet Excel add-in.

1. Creating and configuring an instance of the JRDS

   Hosting providers may need to configure the following items for a Warehouse JRDS. The underlying data source that is used is the Warehouse Data Source, so information about the exact usage of the following settings may be referenced in its documentation:

   InstanceName	Hosting providers may need to configure the following items for a Warehouse JRDS. The underlying data source that is used is the Warehouse Data Source, so information about the exact usage of the following settings may be referenced in its documentation:
   WindowsAuthentication	Whether the Warehouse JRDS instance will connect to the SQL Server specified in the “-Server” parameter via Windows Authentication, or via SQL Authentication. Accepted values are [$true, $false] $true: Uses Windows Authentication. The credentials specified in Jet’s data source settings will be used for authenticating with SQL Server. $false: Uses SQL Authentication. The credentials specified on the JRDS with the “-UserId” and “-Password” parameters will be used for authenticating with SQL Server.
   Server	The name of the SQL Server hosting the warehouse database
   Database	The database name
   UserID	The UserId to use in the JRDS for connecting to SQL Server. Used in combination with WindowsAuthentication [$false].
   Password	The Password to use for the specified UserId to use in the JRDS for connecting to SQL Server. Used in combination with WindowsAuthentication [$false].

    

   The following is an example of creating a new service instance for a customer named “JetReports”, that uses port 8084, and points to a warehouse database that exists on a SQL Server instance hosted on a server named “Jet-Dev-Test”, using an authentication type of SQL Server Authentication:

   Example Commands

   New-JetRemoteDataService -Port 8084

   New-JetWarehouseRemoteDataServiceInstance -InstanceName "JetReports" -Server "Jet-Dev-Test"
   -Database "Warehouse" -UserId "sa" -Password "saPassword" –WindowsAuthentication $false

    

   The bare minimum settings have been configured to make this data service usable.

2. Binding the Security Certificate

   *** IMPORTANT MANUAL STEP ***

   There is a manual step when creating the Application Container which must only be done once.

   When the Application Container is created, no SSL/TLS certificate is configured for it. In order to complete this step, IIS Manager must be opened and the binding on the site entitled “Jet Remote Data Service Container” must be configured to use an SSL/TLS certificate, as shown below

   

   (configuring the SSL/TLS certificate for use with the Application Container)

   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.

   

3. Configuring the data source connection in the Jet Excel add-in