Creating a New Data Source Name (DSN) in Windows on Your Workstation to Connect to Cloud Vista

Author: Eric Vasbinder

Overview

Accessing the Vista database directly, from tools other than Vista itself, is often necessary to enable the types of workflows and reporting that customers desire. For example, being able to use third party tools such as Insight's Spreadsheet Server or Microsoft Excel to perform queries and run reports is an often key part of our customers' needs. To connect to a Vista database to access this data, these tools make use of a mechanism called a Data Source Name (DSN) that, at a minimum, tells the application the name of the server and the name of the database to which it will be connecting. In addition, DSNs can often contain the username and password needed to authenticate to the database in question.

When moving to the cloud, something happens with this setup. I'll bet a number of you observant readers have already picked up on it: the server name and IP address for Vista changes. When you move your Vista server into our cloud as part of Trimble Construction One (TC1), or our older clouds such as Viewpoint One (VP1), Viewpoint Enterprise Cloud (VEC), or Viewpoint for Cloud (VFC), your Vista instance will no longer be running on its previous, on-premise server. This means that the previous DSNs that told Excel, Spreadsheet Server, Crystal Reports Builder, and other tools how to connect to Vista will break.

Ergo, once your Vista database has been moved into the cloud, you'll need to update your DSNs to change three pieces of information:

1. Your Vista server name

2. The ODBC port used by the server

   1. The standard ODBC port of 1433 used on-premise will not work in the cloud when using our preferred TLS Database Endpoint (TLS VPN) - you'll be given a new one.

      1. See the TLS Database Endpoint (TLS VPN) FAQ article for more details.

3. The SQL account needed to log in to the Vista database

The following are the series of steps that detail how to create a new System or User DSN in your Windows workstation, to use by other applications to connect to your Vista database.

IMPORTANT NOTE REGARDING DSNs OVER IPSEC VPNs

Please note that the following steps are written with the use of the TLS Database Endpoint (TLS VPN) to connect to your Vista database over ODBC. If you are connecting via the IPSEC VPN, the instructions will be very similar, save for the following two differences:

1. You will use the private IP address of the Vista server instead of the TLS Database Endpoint (TLS VPN) DNS name. For example, 10.0.1.123, instead of CODE-sql.viewpointdata.cloud.

2. You will need to specify the standard ODBC port of 1433 to connect to the Vista database, instead of a custom port as per the TLS Database Endpoint (TLS VPN) option.

DSN Authentication Information

You can choose to create a DSN without embedding your username and password in the DSN if you wish. If you do so, please be aware that you will be prompted for authentication information each time you attempt to log in to Vista's database. In addition, please note that some tools do not support DSNs without authentication information embedded. As such, instructions below will also show how to add that information to the DSN.

Please note that if you have already created the DSN without authentication information, you may open the DSN list and click the DSN in question. Then click "Configure". If you do so, it will take you back to Step 8 below.

Detailed Steps

Creating the Actual DSN

1. Obtain and write down your TLS Database Endpoint (TLS VPN) DNS name, which should be in the form of XXXX and the custom port number to which you've been assigned.

   1. See this FAQ for more details: TLS Database Endpoint

2. Create a custom SQL account to use for the DSN if you have not already.

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

3. Click on your Windows icon in the lower left of your windows task bar and type in ODBC.

4. If you are using the 64 bit version of Vista on your computer (the majority of new Vista client installs are 64 bit), please click on the 64-bit ODBC Data Sources app.

5. Once the application is open, you should see a list of the User DSNs that are already on your system. If you click on System DSNs, you'll see the system-wide DSNs that are now available.

You now have a choice to create either a System-wide DSN for Vista in the cloud, or a User-level DSN for Vista in the cloud. If this system is a shared system with multiple people using it, such as a Terminal Server or Azure Virtual Desktop, we recommend a System DSN. If you are adding this DSN for the use of an individual user ONLY, then a User DSN is appropriate.

IMPORTANT: Creating System DSNs REQUIRES that you have local machine Administrator privileges. If you do not, please ask your IT Admin for assistance or create a User level DSN instead.

NOTE: We will be creating a user-level DSN for the purposes of this walkthrough.

6. Click the "Add" button.

7. Choose SQL Server and click "Finish" on the window that appears.

8. On the following window, type a name for the new DSN.

• We recommend without spaces as some solutions have issues with spaces in the name of DSNs

9. Type the description.

10. Enter the DNS name for your TLS Database Endpoint (TLS VPN) followed by a comma and the custom port number.

• For example: code-sql.viewpointdata.cloud,4018

11. Click Next.

Adding Authentication Information

12. Click SQL Server Authentication.

13. Enter your username and password for your SQL account in the cloud.

• Follow the steps in this Cloud FAQ to create your SQL account: https://sites.google.com/trimble.com/vista-cloud-faq/home/integration-technology/creating-sql-accounts

14. Click Next

15. The ODBC wizard will now preliminarily test the connection behind the scenes and then display a settings summary screen.

16. RECOMMENDED - please now click the "Test Data Source" button on the setup screen to make absolutely certain your data source will work. Once you have done so, if all is successful, you'll see a test results screen like the below.

Summary

Now that your new cloud Vista ODBC DSN is created, you can reference it in third-party tools as the means to connect to Vista's database in the cloud. Please consult your documentation for those tools on how to reference a new User or System DSN on your workstation.