I need a dedicated SQL account for my integration to Vista in your cloud.  How do I set that up?

Author: Eric Vasbinder

Background and Best Practices

Many client-side integrations to Vista, as well as some server-side integrations into Vista, need to have a SQL account in order to authenticate to Vista in our cloud.  These third party applications have no awareness of the users in our cloud AD infrastructure, and your computers and workstations are not joined to Viewpoint's cloud AD domain.  As such, you will need to create dedicated SQL accounts to allow applications such as Insight Spreadsheet Server (ISS / GSS), Crystal Builder, SQL Server Management Studio, etc. to connect to Vista.

Fortunately, Vista has the functionality built-in to make this easy.  First, however, make certain you have identified your Vista admin user who has access to the VA User Profile form to create users; they will need to perform the following steps.

Please keep in mind that by creating the SQL account through Vista, you will also have created a Vista account for this user.  If this is not ideal for you, you may also use SQL Server Management Studio (SSMS) to create your SQL accounts manually.

NOTE:  You should NOT check "Allow Vista Remote Link login" on VA User Profile.  Your integration account does NOT need to log in to Vista.

IMPORTANT:  For security reasons, you need to create ONE SQL Account per each USER and, if service accounts are necessary to integrate third party server applications into Vista, then you need to have a separate SQL account per application.   In addition, any accounts created should respect the following naming convention:  "username.sql.code" .

For example, if user, "Jen Mason", whose cloud environment for her company has the code "GG8Z", has both Crystal Designer and GSS, and her company uses GCPay, you would see the following:

• • SQL Account "A":  jmason.sql.gg8z

    • Used for Crystal and Spreadsheet Server (GSS)

  • SQL Account "B":  gcpay.svc.gg8z

    • Used for GCPay's server(s) to talk to Vista

You can create the SQL accounts for both types of needs (end users and integrations) the same way using the steps below.

SECURITY ALERT:  This SQL account allows as much access into the core Vista database as you allow.

TAKE CARE TO KEEP THE USERNAME AND PASSWORD SECURE!

NOTE:  Access will ONLY be over a properly configured and approved TLS Database Endpoint (TLS VPN) or IPSEC VPN tunnel.

CRITICAL NOTE:  As mentioned in the TLS Database Endpoint (TLS VPN) FAQ Article, the TLS Endpoint is ONLY Available for customers on our most modern ERP Cloud offering, known variously as Trimble Construction One (TC1) or Viewpoint One (VP1) or VRL Cloud.  It can also, with approval, be made available on our most recent legacy cloud Viewpoint Enterprise Cloud RDP (VEC RDP).  

It is NOT available on Viewpoint for Cloud (VFC).  These customers MUST use an IPSEC VPN ONLY to access the Vista database directly.

Creating a SQL Account

Here are the steps to create a dedicated SQL account in Vista:

1.  Log in to Vista’s VA User Profile form

2.  Click icon to create new user.

3.  Create a new user named “first.last.app”, where .app refers to the application being used via SQL. For example, John Doe’s GSS SQL account would be john.doe.gss

4.  If this account is ONLY intended for use by a non-Vista user - if no Vista client access will ever be needed by this account, you can change the User Type in the dropdown in the upper right of the Info tab from Vista only to "User Application".  Doing so will prevent the new SQL account from logging in to Vista directly, even if the "Allow VRL login" is checked.

• For example, if you have an account that is intended to be a pure SQL service account, used by a third-party system such as Insight's Spreadsheet Server, Prophix, etc., that account will NEVER need to access the Vista client directly and should have its "User Type" set to "User Application" to improve the security of that account.

NOTE:  Please UNCHECK "Allow Vista Remote Link Login" above if this account is for use by an external application, such as Spreadsheet Server, SSMS, etc..  This is NOT NECESSARY for SQL Service accounts for third party integrations.

4.  Click Save

5.  You will get an alert that Vista needs to create a SQL account for this user - PERFECT.  This is exactly what we need.  Click Close on this alert.

6.  Enter the password to create the SQL User - set a strong password please.

7.  You will now need to copy the security permissions from the user that has access to what you want from Vista.  To do so, click Vista Copy Security button.

8.  Choose the user from which to copy the security settings and click OK.

Once complete, your new application and user-specific SQL service account will be created and available for use with your integration.

Security Tips

Before finishing the set up of your SQL account, it would be beneficial to understand the level of access which should be granted to this account.  For example, if this account is intended to be used for an actual human to create stored procedures, triggers, views, etc. in SSMS, it may be wise to grant this account DBO privileges for the Vista database (a support ticket will be required to grant DBO).

Best Practice Permissions - Least Privilege

In some cases, you may have a third party-integration that requires the ability to read multiple tables within Vista's database.  Please note that it is strongly preferred for permissions to be granted in the following manner:

1. The vendor of your application MUST be aware of the specific tables and views to which their integration needs access.  They then provide that list of required permissions to you (the customer).

2. You can then enable that access by using Vista's built-in security roles and permissions.  

Alternative Permissions - Less Secure but Easier

However, oftentimes, it is extremely difficult for vendors of third party products to provide customers with a detailed, accurate, and up to date list of the Vista database tables and views needed.  As such, submitting a ticket to grant this account "db_datareader" privileges may be advised, at least until your application vendor can provide a detailed permissions requirements list.   For tools that require broad-based write privileges, "db_datawriter" may be a required security role for this account.

Please note that as part of a good defense in depth security strategy, we highly recommend choosing complex passwords, with access limited by the principle of least privilege.  For example, an account intended for Global Spreadsheet Server should NEVER be granted write permissions as Spreadsheet Server is a financial reporting tool that needs no write access.

Please note that if an account already exists within Vista that has the proper privileges needed for this new SQL account, you can use the Copy Security button to copy permissions to this account; Vista Groups and security permissions can also be effective.

The following security permissions however do require that you submit a cloud support case in order to request these permissions:

• DBO

• db_datawriter

• db_datareader