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 security based on groups becomes difficult as well.

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.

Authorization with Jump Hosts

Even when using the SQL Jump Host, due to the fact that the SQL Jump Host and the Vista SQL server are now in two separate domains there are authorization limitiations that must be addressed.  

In regards to authorization, the most important consideration is if row level security (a.k.a. "Data Type" security) is needed for queries directly to the SQL database.  If row level security is not in use today, or can be made redundant through judicious access controls on direct SQL connectivity, the solution becomes much more simple.   If, however, row level security is required, then the authorization situation becomes more complex, requiring additional management for customer IT staffs.

The following two paths are therefore to be considered for authorization when using a SQL Jump Host.

Option A - NO Row Level / Data Type Security

In this scenario, security is provided at the view level only.  As such, all customers need to do on their jump host is to limit access to those views on the SQL jump host.  This is all done locally, within the customer network boundary, using local AD groups to assign access to views and then assigning AD users to those groups.  On the back end, access is provided through a pure SQL service account that uses the SQL linked server object; a SQL service account that has db_datareader privileges.

Option A - Data Flow Authorization Model

• End User to SQL Jump Host Access Control:  Locally controlled views, tied to AD user groups

• SQL Jump Host to Vista DB Server:  single SQL Service account with db_datareader (or similar more broad-based permissions)

Option B - Row Level / Data Type Security Required

In this scenario, security MUST be provided at BOTH the view and row level.  As such, since we need to now pass through some context of the end user's identity to provide access to specific rows, we can no longer make do with a pure SQL Service account to talk from the Jump Host to the Vista SQL server.  Instead, we must begin to provide user mapping that will associate the end user's local AD account with a corresponding account in the Vista DB; an account with the appropriate row level (data type) permissions.  This consists of multiple steps, including making a copy of the end user's existing Vista account as a SQL account, ensuring the end user has access to it, and then copying security from the end user's existing Vista application account to the new SQL account for that end user.

Option B - Data Flow Authorization Model

• PREREQUISITES:

  • Each human end user connecting over the jump host MUST have a brand new SQL account (User type of "User Application" in VA User Profile) created on the Vista cloud server (instructions here:  https://sites.google.com/trimble.com/vista-cloud-faq/home/integration-technology/creating-sql-accounts).

  • Security permissions must be copied from their existing, regular Vista account to this new, SQL-only account.

• End User to SQL Jump Host Access Control:  Locally controlled views, tied to AD user groups

• SQL Jump Host to Vista DB Server:  Multiple user mappings created on Linked Server Object, mapping local users to remotely created SQL accounts.

  • Please see linked article in the Prerequisites section to learn more about setting up SQL accounts in the cloud for end users to connect to the SQL server.

Mitigations to Reduce Level of Effort and Complexity

As we can see above, enforcing data type security (row level security) with direct ODBC queries to the Vista database is where the lion's share of complexity and maintenance work originate.  Given that knowledge, the best practice recommendation is to use the following to eliminate or severly limit the need to respect row level security with direct database connections:

• Limit the number of processes that require querying the Vista database directly by leveraging options such as:

  • Using more Vista application technologies such as SSRS and Crystal Reports

  • Leveraging TC1 Analytics dashboards, TC1 hosted paginated (SSRS) reports, and TC1 hosted Crystal reports

  • Leveraging our AppXchange connectors and Vista REST API to shuttle data into our own AppXchange datawarehouse

  • Leveraging external data flow and analytics systems such as Fabric tied to PowerBI and/or Azure Data Factory

• Limit the number of human end users who need direct ODBC database access to database, by leveragins options such as:

  • Grouping read only access into "chunks" of end users who need access to certain blocks of jobs, vendors, etc.

  • Focusing direct database access into the hands of only a few power users

Recommendation Thoughts

Please note that if you absolutely need row level (data type) security on more than 25+ human end users who access the Vista SQL database, the SQL Jump Host may NOT be an ideal solution for you as you will be increasing your maintenance burden unnecessarily.   Effectively, if a customer's IT staff needs to manually create user accounts, assign and manage passwords, and copy security for a multitude of end user SQL accounts to allow for row level security to be respected, why not allow direct access to the Vista database over the IPSEC VPN or TLS Database Endpoint (TLS VPN) for those end users?

That said, there are a couple of reasons that you may wish to abstract that end user access away from the Vista DB directly:

1. For improvements in security by allowing for the Jump Host to be an additional access filter for access to the Vista DB

2. To allow for a smoother transition, if you are well on the way of moving toward a more data centric model that involves migrating to APIs, PowerBI, Data Factor, TC1 Analytics, and other more modern data retrieval solutions in the near term: it serves as a means to wean organizational processes and tooling off of direct DB access.

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. If using Row (data type) level security:

   1. Ensure SQL Service accounts are created for human end users on teh Vista SQL server

      1. https://sites.google.com/trimble.com/vista-cloud-faq/home/integration-technology/creating-sql-accounts

   2. Ensure that the Copy Security fuction is used to copy security from the user's existing Vista account to the new one to be used with direct SQL queries

   3. Map each newly created Vista SQL account with the local human end user's AD account, using the Linked Server configuration screen in SSMS for your SQL Jump Host

7. If NOT using Row level security:

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

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