TLS Database Endpoint

Author: Eric Vasbinder  

Article Cloud Applicability:

• Vista VRL (TC1/VP1)

This article applies only to those ERP clouds listed above.

CONTENTS

IMPORTANT AVAILABILITY NOTICE

CRITICAL NOTE:  The TLS Database Endpoint (TLS VPN) is ONLY available for customers who are hosted in our Trimble Construction One (TC1), Viewpoint One (VP1), Vista SaaS, or Viewpoint Enterprise Cloud (VEC - RDP).  

Viewpoint For Cloud (VFC) customers cannot use the TLS Database Endpoint (TLS VPN)

In the Past

While accessing your Vista installation using the Vista rich client is as simple as installing the client on your local PC, connecting third party products over the Internet to our Viewpoint One / Trimble Construction One cloud is more difficult.  

Third party products, such as Insight Software's Spreadsheet Server, Microsoft Excel, PowerBI, Crystal Reports Builder, and SSMS need to communicate directly to the Vista database using ODBC:  they cannot use the Vista client's Vista Remote Link (VRL) connection over HTTPS.  In the past, we have enabled this connectivity by standing up an IPSEC VPN tunnel between the customer's network and our cloud, allowing for a direct, secure tunnel for these third party products to use.  

Unfortunately, VPNs come with their own potential drawbacks.  VPNs can often be difficult to set up and troubleshoot.  In addition, VPNs connecting to our cloud in Microsoft Azure MUST support the connection protocols and standards required by Microsoft, which can often require customers to upgrade the software on their Internet gateway devices, or even purchase and install new hardware.   The new TLS Database Endpoint option changes this need and dramatically simplifies the process of setting up third-party integrations to the Vista server in the cloud.

A New Option

This new TLS Database Endpoint method for connecting directly to the Vista database significantly reduces the need to stand up VPNs, potentially eliminating the need for many customers to set up a VPN entirely.  The TLS Database Endpoint technology provides a secured, TLS encrypted connection that is protected by IP Address whitelisting and Azure Deep Packet inspection; a connection that works directly over the Internet with no VPN required.  This method provides the ability to connect directly using ODBC over TLS to the Vista database from third party applications with no special configuration required other than the DNS name of the TLS Database Endpoint proxy server.

IMPORTANT:  The TLS Database Endpoint (TLS VPN) is Available Now for Your Integration Needs

Caveats

• The TLS Database Endpoint (TLS VPN) provides ODBC access ONLY

  • Port 1443 is the standard, but MOST EVERY CUSTOMER WILL HAVE A CUSTOM PORT.  PLEASE CHECK WITH YOUR CLOUD ENGINEERING CONTACT TO DETERMINE YOUR SPECIFIC PORT

• Port 445 (SMB) access is NOT POSSIBLE over the TLS Database Endpoint (TLS VPN)

  • Autoimports and file sharing via windows file networking are thus NOT possible through the TLS Database Endpoint (TLS VPN)

• If your connection is not coming from a pre-approved IP address, it will be blocked for security reasons

  • This usually means that employees who are working at home MUST be connected to their corporate client VPN in order to leverage the TLS Database Endpoint (TLS VPN) connection to Vista from the main corporate network.

Costs

Due to the costs of standing up a VPN tunnel given to Trimble Viewpoint from Microsoft, we need to pass on those costs in the form of a small VPN fee on TC1/Viewpoint One contracts.  This is non-negotiable and applies to both TLS Database Endpoint (TLS VPN) and IPSEC VPNs.

Requirements

The TLS Database Endpoint mechanism requires a few items in order to work properly:

• SQL Service Accounts:

  • Each third party integration that talks to the Vista database directly should use a SQL service account to allow for the connection to work.

    • Here is the relevant cloud FAQ article:  I need a dedicated SQL account for my integration to Vista in your cloud.  How do I set that up? 

• Direct Connectivity to the Internet (NO PROXIES OR ZSCALER TYPE SOLUTIONS)

  • We have found with multiple customers that proxy servers and zScaler type solutions cause significant problems with connectivity when using this method.

  • Proxy servers and zScaler type solutions are designed to add an additional layer of security onto network traffic.  As part of their standard operations, however, they modify the flow and addressing of network packets.  While this can be acceptable for HTTPS traffic, for more advanced connectivity, such as ODBC over a VPN, problems can ensue.  We have seen multiple occasions where the way these solutions operate causes issues with our security mechanisms used for the TLS Database Endpoint (TLS VPN) and the IPSEC VPN, including IP address pre-approvals ("whitelisting").

  • As such, we require that ALL TRAFFIC intended for our cloud VPNs (DOES NOT APPLY TO VISTA CLIENT or HFF/TEAM/ANALYTICS/KEYSTYLE) be forced to be routed directly to the Internet, through a single network interface on your gateway device that is assigned a static, public IP address, with NO PROXY or zSCALER style solutions in between.

    • This applies to each site that you would like to use the IPSEC VPN or TLS Database Endpoint (TLS VPN) to connect to Vista in our cloud.  For example, if you have a site in Chicago and a site in Phoenix that you would like to have direct access to the Vista database, you'll need to ensure that both Chicago and Phoeix have a static, public IP address for their Internet gateway and that traffic intended for our cloud is forced to be direct routed to the Internet, avoiding all proxies and / or zScaler type solutions.

    • This avoids the issue entirely and dramatically improves the reliability of the connection.

  • Fortunately, due to the high trust nature of the traffic coming from the Trimble Viewpoint cloud and the defined characteristics of connections to our cloud, there are few negative aspects to exempting our traffic from zScaler or proxy solutions.

• Static IP Address:

  • Due to IP address whitelisting requirements, this method will ONLY work if you have a static IP address from which the network traffic to the Vista database will come.  For example:

    • A static IP assigned to your local Internet gateway from your ISP

    • A static IP on the third-party server from the integration partner

    • A static IP on your cloud server that will talk to Vista

  • CRITICAL NOTE:  A static IP can be added to ONLY ONE customer environment. 

    • For example, if you have a test environment and a production environment, your Internet gateway's IP can be used with ONLY ONE of those two environments for the TLS Database Endpoint (TLS VPN).  This restriction is most heavily encountered when third party consultants, who often access multiple client environments, are involved.  If you are or need to be involved with third-party consultants, please review the following article on third-party consultant access:  Cloud Access for Third Party Consultants .

• Allow Bi-Directional traffic over SPECIFIED PORT (MAY VARY AS NEEDED) through your Firewall

  • Traffic needs to be allowed to the DNS name (we use a static IP for this DNS name) for the Vista TLS Database Endpoint

  • Traffic also needs to be allowed come from the TLS Database Endpoint to the server(s) and systems in your network that will be communicating with Vista

    • NOTE:  If multiple users in your network will be making use of this connection to Vista, then you will most likely be better served by providing us with the static IP address to your network ingress/egress point; your Internet gateway router.

  • Please note that since only one IP and port combination can be allowed at a time, ALL customers will be assigned a TLS Database Endpoint (TLS VPN) that uses a different port than the standard port 1433.  Please contact your Viewpoint cloud engineering or transformation contact to ensure you have the correct DNS name and port number.

Caveats with Outbound Origination Connections

I.E. Connections FROM Vista to a Remote Site Over the TLS Database Endpoint (TLS VPN

The TLS endpoint is a one-way initiation connection.  In other words, connections are supported when they are made from an external solution to Vista using the TLS endpoint.  However, the converse approach of initiating a connection FROM Vista to an external data source or SQL linked server is not supported over the TLS endpoint.

In these scenarios where connections need to be made FROM Vista to another solution an IPSEC VPN is the appropriate method to choose.

Setting it Up

To set up your TLS Database Endpoint, please create a cloud support ticket in your Viewpoint Support portal.  Please include in the ticket the static IP addresses from which your Internet traffic will come.   We pre-approve these IP addresses.  

Once the TLS Database Endpoint has been set up, you will be given the DNS name that you will use to connect to the Vista database.  Then you merely need to use that new server name in the configuration for each third-party application.

Caveats With DSNs

Many reporting tools that directly access the Vista database, such as SSRS Reports and Crystal Reports, use Data Source Names (DSNs) in order to point the report to the correct database to query.  When you create or edit a report to work in our cloud, you need to specify a DSN that is appropriate for your server name in our cloud.  Usually, those are in the form of CompanyCodeD1.  For example, if your company code is IAKS, the server name in the DSN for your reports would normally be "IAKSD1".  

Unfortunately, if you're using the TLS Database Endpoint (TLS VPN) to connect to the Vista database to edit reports, with Crystal Reports Builder and SSRS Reports Builder housed on your local workstation, you'll encounter a problem.  Specifically, your local workstation, which is where you run those tools, has no clue how to resolve that server name to the server's actual IP.  Remember, our cloud uses different DNS servers than your local workstation.  Fortunately, the solution is pretty straightforward:  merely edit your local workstation's HOSTS file to allow it to properly resolve the server's public IP to the name you need to use for the DSN.

Determining the IP Address to Use

Fortunately, the IP address specified by the DNS name for your TLS Database Endpoint (TLS VPN) is static; it will not change unless you change datacenters.  As such, once you have completed this process, it will usually not be necessary to repeat it.  The first major part to this process is determining the static IP of the TLS Database Endpoint (TLS VPN), which can be set to resolve for the server's D1 name in your HOSTS file on your local workstation.  There are two ways to do this:  ask your Viewpoint / Trimble Support representative or Cloud Transformation team member for the IP, or you may use the simple steps below to determine the IP.

Steps:

1.  Open a command prompt, terminal window, or Powershell window.

2. At the command prompt, type in the "ping" command followed by the name of your TLS Database Endpoint (TLS VPN).  For example, if your company code is IAKS, then you would type in the following:

   1. ping IAKS-sql.viewpointdata.cloud

3. The results will not show any connection, however, the actual IP address shown will be that of the TLS Database Endpoint (TLS VPN) used for your environment.

Editing the HOSTS File

Once you have the IP you need, all you need to do now is to edit the HOSTS file to allow for the DSN name that will work on our servers to also resolve properly for your locally hosted SSRS Reports Builder and/or Crystal Reports Builder.

IMPORTANT

You will need local computer ADMINISTRATOR privileges to edit this file.  That is REQUIRED for this to work.

Steps:

1. Browse to C:\Windows\system32\drivers\etc in your File Explorer

2. Open the "hosts" file in your favorite text editor.

  3.  At the bottom of the HOSTS file, press the tab button, then add in the IP Address, then press tab again and add in the CompanyCodeD1 item, as per the below screenshot.

  4.  Once added, save the file.

  5.  The changes you have made should be instantaneous and can be verified by attempting to ping the server by the new CompanyCodeD1 name that you just entered.  If the name resolves correctly and your computer attempts to ping the server, even though it cannot actually reach it, you will know you have succeeded.

Ports and DSNs

As mentioned earlier in this article, you will be assigned a custom port for your ODBC traffic over the TLS Database Endpoint (TLS VPN).  Various different tools accommodate the use of custom ports using different syntax to separate the DNS name and the port.  In addition, various tools require even standard port 1433 to be specified.

As such, we recommend that when you set up connectivity using SSMS, Crystal Reports Builder, Insight's Spreadsheet Server (GSS), and others, you should always specify the port where standard or not.

Here are some examples of port formats to use, assuming a custom port number of 5180 and a company code of YYYY:

• yyyy-sql.viewpointdata.cloud:5180

• yyyy-sql.viewpointdata.cloud,5180

• yyyy-sql.viewpointdata.cloud 5180

If one of the above formats does not work, please try another.  Data Source Name (DSN) connection strings in various tools can be especially problematic, requiring a comma between the port and TLS Database Endpoint (TLS VPN) DNS name.

Custom Ports in Detail

As mentioned above, each customer environment is assigned a unique port number.   This port number is the mechanism used by our Azure based cloud to determine to which Vista server to send your request.  As such, please NOTE that the custom port you are given will be specific to EACH environment.  Thus, if you have two environments in our cloud, Test and production, you will have a unique port number for each environment. 

This might be confusing to some people as the environment company code listed at the beginning of the server name (e.g. IAKS-sql.viewpointdata.cloud) does appear to be unique for each environment.  However, on deeper inspection we find that two different company codes for the same customer will route to the same Azure IP address.

Thus, the critical item that gets you into the specific environment is the port.  Ergo, you will need to use the correct port to ensure that you are connecting to the proper database server.  For example, if Production is port 5180 and Test is port 5181, you would need to make absolutely certain that the connection string you are using for each environment looks like the following:

 • Production:  PROD-sql.viewpointdata.cloud,5180

 • Test:  TEST-sql.viewpointdata.cloud,5181

Please keep in mind that if you attempt to connect to PROD-sql.viewpointdata.cloud,5181 you might think you are connecting to your production instance, but instead, you would be connecting to your test environment, as the port number is specifying test.

Logistics

NOTE:  Each customer will have their own, unique DNS name for their TLS Database Endpoint.

The logistics that you will need to know are the following:

• DNS Name format options (select based on tool's syntax requirement):

  • COMPANYCODE-sql.viewpointdata.cloud:PORT

  • COMPANYCODE-sql.viewpointdata.cloud,PORT

  • COMPANYCODE-sql.viewpointdata.cloud PORT

  • REMINDER:  YOUR TLS DNS NAME WILL NOT BE ACTIVE YOU HAVE CONFIRMATION FROM TRIMBLE VIEWPOINT THAT SETUP IS COMPLETE

• Bi-Directional Port to Open:  TCP 1433 (THIS PORT WILL BE DIFFERENT - PLEASE CONFIRM)

• Protocol:  ODBC over TLS Encryption

• Remote Endpoint Requirement:  Static IP for Whitelisting Purposes

  • Please provide the static IP of the server or network gateway which needs to be whitelisted for access to the Vista database over this connection

    • For example, the static IP that a customer’s ISP provides for their Internet gateway router

• Testing of this connection should take place from the network behind the whitelisted IP, using a TCP connection to your custom port

  • Options for testing include:

    • Powershell command (PREFERRED FOR WINDOWS CLIENTS):  Test-NetConnection COMPANYCODE-sql.viewpointdata.cloud -port XXXX

      • Where "XXXX" is your port number.  Please reach out to your cloud engineering contact for more details.

    • Telnet to TCP port (PREFERRED FOR macOS and LINUX CLIENTS):  telnet COMPANYCODE-sql.viewpointdata.cloud XXXX

    • The following two methods merely confirm if the port is open, not if the server is actively pending connections on that port, thus these two methods are not quite as useful as either “tnc” on Powershell or Telnet on macOS / Linux:

      • TCPPING to TCP port:  tcpping -c -x 5 COMPANYCODE-sql.viewpointdata.cloud XXXX

      • PSPING to TCP port:  psping -t -4 COMPANYCODE-sql.viewpointdata.cloud:XXXX