Creating a New Data Source Name (DSN) in Microsoft Excel

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 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 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:

• Your Vista server name

• The ODBC port used by the server

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

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

• The SQL account needed to log in to the Vista database

The following are the series of steps that detail how to either create a new DSN in your Excel files or to leverage an existing System or User DSN on your Windows workstation, to allow those spreadsheets to connect to Vista in the cloud.

Option A - Creating a New DSN Embedded in Your Excel File

This option has the benefit of being able to be portable to multiple users across your environment. Please note however that, for security reasons, a user is not allowed to connect to a Vista database over the TLS Database Endpoint (TLS VPN) without having had their public, static IP address pre-approved, or whitelisted. If your overall corporate network's main Internet router has already been pre-approved and you're on the corporat network when you attempt to use your Excel files, you're good to go. If you're at home (or coffee shop, or hotel, etc.), the following options will work:

• Option 1 - Use your Corp VPN: Use your company's client VPN to connect to your corporate network and force all communications to go over that VPN. This will make all the traffic to Vista look like it's coming from your company, not your home.

• Option 2 - Use your Terminal Server / Azure Virtual Desktop: If your company has an Azure Virtual Desktop or Terminal Server for your use while roaming, and if that device has a static IP that has been pre-approved, you'll be able to log in to that to connect.

• Option 3 - Add your IP to the whitelist: This option only works if the place you're at has a static, public IP address for connecting to the broader Internet. If you create a case with our Trimble Viewpoint cloud support team, we can add this new IP address to the pre-approved whitelist.

Option A - Detailed Steps

The following steps will walk you through how to add a new DSN to your Excel files.

1. Open Microsoft Excel.

2. Open the spreadsheet to which you would like to add a connection to the Vista database.

3. Click on the "Data" option in the ribbon bar.

4. Click on the "Get Data" option that should be on the left.

5. Hover over "From Database", then when the menu opens on the right, click on "From SQL Server Database".

6. Enter the server name from the TLS Database Endpoint (TLS VPN) and follow that up with a comma and the custom port number.

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

7. Enter Vista's actual database name, Viewpoint

a. Please note that the name of the Vista database will almost ALWAYS be "Viewpoint"

8. Click OK.

9. The next screen will allow you choose the method you'll use in this Excel file to log in to the Vista database. In this case, you'll choose "Database".

10. Next enter the username and password for the SQL account you've created for this purpose. Please see here for instructions on how to set up the SQL account if you have not already done so: https://sites.google.com/trimble.com/vista-cloud-faq/home/integration-technology/creating-sql-accounts

11. In the dropdown below, choose the name of the connection that you set up in the previous screen.

12. Click "Connect".

12. Once you have successfully connected, you'll see Excel pull up a list of all the tables available to query in the Vista database.

13. Choose the Table or Tables to which you would like to connect.

14. You may then select to "Load" the data into the Excel file directly, or to Transform it instead.

Option B - Using an Existing User or System DSN from Your Workstation

This option has the advantage of being the fastest to set up once it has been configured on your workstation in a previous set of steps: Creating a New Data Source Name (DSN) in Windows to Connect to Cloud Vista

Option B - Detailed Steps

The following steps will walk you through how to add an existing System or User DSN on your workstation to your Excel files.

1. Open Microsoft Excel.

2. Open the spreadsheet to which you would like to add a connection to the Vista database.

3. Click on the "Data" option in the ribbon bar.

4. Click on "Get Data", then hover over "From Other Sources".

5. Click on "From ODBC".

6. On the next screen, you'll see an option to choose the DSN you previously created in the drop down list.

7. Click "OK" after selecting the DSN from your local computer.

8. If you have already stored your username and password in the DSN on your workstation, the next screen you'll see will be the screen to choose the tables in Vista from which you would like to pull data.

IMPORTANT

If you did NOT store your username and password credentials in the DSN on your computer, you'll need to enter them in an additional screen in Excel after step 7 above. You'll enter your username and password for the SQL account that you have already created, then click "Connect".