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

Author: Eric Vasbinder

Best Practice Restrictions - Avoid SQL Accounts Where Possible

Though this entire article is predicated around enabling you to have access to the Vista database via SQL directly, we no longer recommend this method due to its complexity and the difficulty in managing this level of access over the long term.  

For a more "future proof" way to integrate into Vista, we highly recommend investigating either AppXchange / DataXchange (Preferred), or the Viewpoint API (deprecated).  These mechanisms provide a more robust and controlled way to access Vista data, through either pre-built integrations or REST API endpoints.

If you MUST use direct SQL accounts to access your data, please read on to become familiar with how to set up and secure those accounts.

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) - Client Side App to create your SQL accounts manually.

NOTE:  Creating SQL accounts in SSMS requires the following privileges be assigned to your SQL login:

“ALTER_ANY_USER”, “ALTER_ANY_LOGIN”, and be granted membership in the LoginManager Group.  

Alternatively, you may assign the "db_securityadmin" and "db_accessadmin" and "db_owner" roles to that new user account.

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.

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

Two Tier Security Permissions

Security as it applies to authorization within Vista accounts can be envisioned as being two tiers:  Application level and SQL level.  Each one of those is controlled via different mechanisms:

• Application Level security

  • Consists of:  access to forms, data types, records, etc. within the Vista Application itself

  • Provided through: Vista user group membership

  • Who can edit:  Customer security admins, through Vista itself, using tools like "Copy Security"

• SQL Level Security

  • Consists of:  access to raw tables and views in SQL itself

  • Provided through:  SQL roles (e.g. db_datareader) and manual SQL account permissions

  • Who can edit:  Viewpoint Cloud Support personnel

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 reaching out to the Trimble support team and creating a support case requesting that specific security in SQL be applied to that new SQL account.

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 the Vista SQL database that has the proper privileges needed for this new SQL account, AT THE SQL LEVEL, you can reach out to the Trimble Viewpoint support team to have them copy the permissions from the previous account to this new one.

For purely application level data authorization, you may 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

VCSUser Role Caveat

Please note that when creating SQL accounts via this method, there are certain built-in security restrictions, added in a recent Vista release, that will apply to these new SQL users regardless of whether they have DBO permissions or not.  For example, if a DBO user, created by this method, attempts to Alter a View in the Vista database, they will be denied since they are a member of the VCSUsers role, which restricts the ability to edit arbitrary views.

To that end, we recommend that if you will be using this account to make edits to the Vista database over SSMS, and do NOT need to access the Vista application directly using this account, that you remove this new SQL account from being a member of the VCSUsers role.

Assuming that you have a SQL service account with DBO privileges, you can remove the new SQL user from being a member of VCSUsers with the following SQL Script, which can be executed using SSMS:

USE [Viewpoint]

GO

ALTER ROLE [VCSUsers] DROP MEMBER [name.sql]

GO

ALTER ROLE [db_owner] ADD MEMBER [name.sql]

GO 

Code Snippet 1:  Remove membership in VCSUsers for SQL service account.  

Permissions to Manage Other Users Via SSMS

When creating SQL service accounts through this method, please note that, in addition to being a member of VCSUsers by default, these user accounts will NOT have the ability to manage other user accounts in SQL by default.  As such, for SQL accounts created by this method to be pseudo-SQL admins, you'll need to have the following changes performed to your SQL account in question:

1. Drop your user from being part of the VCSUsers Role.

2. Add this new, budding admin account to the role "db_accessadmin".

3. Add this new user to the "db_owner" role for the databases needed.

4. Add this new user to the "db_securityadmin" role for the databases needed.

These items MUST be performed by Trimble cloud personnel and documented in our systems.  As such, you will need to have one of your users who is an authorized Trimble support portal user create a cloud support case at https://support.viewpoint.com describing the items that are needed.  Feel free to copy and paste the request items from the list above.