Active Directory Windows Authorization with a SQL Jump Host

Author: Eric Vasbinder

Applicable ERPs (This article Applies to the following Trimble Viewpoint ERPs):

• Vista

• Spectrum (Uncommon)

• ProContractor (Extremely Rare)

Overview

When a customer considers the totality of their ERP experience, especially with our more enterprise-class ERPs, such as Vista and Spectrum, they will not only envision the ERP itself, but the various third party tools that integrate with the ERPs for functions such as ad-hoc reporting, time keeping, sub contractor payments, and more.  These third party "integrations" are often set up to use a customer's local Active Directory for authorization.  In other words, the IT for our customers, to save time and simplify management, will often set up groups of users in Microsoft Active Directory; groups to which access into specific areas of the ERP is given as needed for the integration.

Cloud Ramifications

When moving to the cloud, the actual location where the ERP software runs changes from servers that the customer owns and manages to servers that Viewpoint owns and manages.  As such, the ERP will no longer be on a server that is a member of the customer's Active Directory (AD) Domain after moving the ERP into the cloud.  The fact that the ERP is no longer a member of your local AD after moving to the cloud means that any system that used local AD to authenticate to the server or to grant access to data on the server will no longer work once your ERP is in our cloud.

Example Workflow and Impact

A good example of this behavior is a common situation whereby a customer may use a large number of Microsoft Excel-based spreadsheets as reports or even interactive ways of pushing data into the ERP's database.  When on-premise customers may have set this up to leverage the local AD account of the end user to both authenticate that user to the ERP's database AND to grant access (authorization) for only the specific data to which that user would be allowed to access.  This authorization is often set up using user groups.

Once the ERP server is in the cloud, this AD-based authentication and authorization will NOT work as the cloud server is no longer managed by your IT.  Fortunately, for logging into the actual ERP or associated tools such as Vista Web, our cloud fully supports federated Azure AD logins (see this cloud FAQ for more details: How does authentication work in your cloud?  Can we use Single Sign On (SSO) with our local AD or Okta?).  However, this support for SSO only extends to the application layer of the ERP and associated tools; server-level components and database server instances in Microsoft SQL do NOT support this Trimble ID SSO method to federate to Azure AD or Okta, etc.

Thus, it becomes difficult once the ERP is moved to our cloud to allow access for end users to leverage Microsoft Excel based spreadsheets that were written to use AD for authentication and for authorization and data access control.  

Standard Approach

Our normal approach for dealing with this access is for the customer to set up SQL service accounts for items such as reporting:  I need a dedicated SQL account for my integration to Vista in your cloud.  How do I set that up?

This method works great for smaller customers, or customers with a limited need to access the database outside of the ERP.

However, this approach loses its appeal if a customer has more than a few users that need specific, delineated access into the ERP's database directly; managing hundreds of SQL accounts is no easy task.  In addition, setting up data type security-based authorization based on groups becomes difficult as well, requiring the customer's IT to track authorization on a per user basis in the SQL server itself.

Alternative Approach - SQL Jump Host

In order to avoid these issues, there is an alternative approach available, originally developed by one of our more astute customers.  This approach involves setting up a SQL Server jump host that will sit on a SQL server that remains in the customer's AD management domain.  This SQL jump host retains a copy of the ERP's database, but uses database synonyms to point the actual queries to the remote, cloud hosted database.  That jump host has connectivity provided to the cloud server through an IPSEC VPN, which allows for data to be pulled from the cloud server when a query is presented to the jump host.

Authentication and Authorization to the customer's SQL Jump Host is provided through Active Directory (AD) user groups, just as today.  Ergo, an end user's workstation would merely need visibility to the Jump Host residing in the customer's network.  No major changes to reports or other items that leverage AD would thus be needed.

Deployment Diagram

Please see the following high level deployment diagram for a view of how this is deployed:

CRITICAL NOTE - EXPERT IT RESOURCES HIGHLY RECOMMENDED FOR SETUP

The steps to set up this deployment model are more complex than most processes in the Trimble Viewpoint ERP Cloud.

We highly recommend coordination with expert IT resources either on staff or through a third party IT consultant.

High Level Setup

The following steps are those recommended tasks needed in order to set up this workaround:

1. Set up an IPSEC VPN to connect the location where your SQL Jump Host will be located and the Trimble Viewpoint environment where your ERP will be located.

   1. NOTE:  It MAY be possible to use the TLS Database Endpoint (TLS VPN) instead of an IPSEC VPN, but this has NOT been tested for feasibility or performance.

   2. Again, as mentioned above, for performance reasons, we recommend setting up this Jump Host in the same Microsoft Azure datacenter where your Vista instance is hosted.  You may need to set up an Azure Subscription and Tenant.  Again, please reach out to expert cloud-hosting IT resources for additional guidance if needed.

2. Set up the Windows Server OS and join it to your actual AD domain.

3. Set up a Microsoft SQL Server of at least SQL 2016/2019 version age on that server from step 2. 

4. Catalog the view query references used by your integrations, including Excel reports, etc.

5. Create a set of SQL synonyms that will point queries to the SQL views specified in the previous step to the cloud ERP server, using either:

   1. Private IP address if using IPSEC VPN

   2. DNS name and port if using the TLS Database Endpoint (TLS VPN)

6. Create a SQL Service account in the cloud ERP server to allow the SQL Jump Host to communicate with cloud ERP.

7. Set up the SQL Jump Host to use the SQL service account to authenticate with cloud ERP database.

8. Update all integrations that currently reference the on-premise ERP server to reference the new SQL Jump Host.