SQL Server Management Studio (SSMS) - Client Side App

Author: Eric Vasbinder

Support Status:  Supported

Integration Method (RDP Customers - e.g. VFC and VEC RDP Published Apps): Hosted Client

Integration Method (VRL Customers): Customer Client with VPN

Hosted:  YES (RDP Customers) / NO (VRL Customers)

Additional Charge:  NO

Product Description: 

Microsoft's SQL Server Management Studio allows for system administrators, report writers, and others to directly access a SQL database and update tables, custom fields, triggers, stored procedures, and more.

Integration Method:

As a client-side app, SQL Server Management Studio (SSMS) can either be hosted with Viewpoint on our terminal servers or hosted on the customer's own local workstations.  Which method is used depends on whether the customer is using Vista Remote Link (VRL) to connect to Vista in the cloud.  If the customer is using VRL, then all client-side applications remain hosted locally on the customer's workstations.  If the customer is conversely using our RDP-based solutions (e.g. Viewpoint For Cloud (VFC) or RDP Published apps in VEC), then we need to host SSMS on our terminal servers for you.

Using SSMS in RDP Clouds (VFC / VEC RDP):

In this case, Viewpoint hosts and then publishes this application as a published / remote app for each end user who needs SSMS access.

Using SSMS in VRL Cloud:

In this case, the customer has no RDP capabilities, as with VRL, those are not needed; all client-side apps are run locally, on the customers own local workstations.  In this case, SSMS would be installed on each local client workstation that needs to use SSMS.  Of course, for SSMS to be able to talk to the Vista database in this situation, a VPN will need to be set up between the customer's environment and the Viewpoint network.  Now that the TLS Database Endpoint (TLS VPN) is ready, we recommend that approach.  If needed for other reasons (i.e. direct SMB file system access for autoimports), an IPSEC VPN may also be used.

Steps:

• • 1. Set up VPN (CUSTOMER IT):  Instructions on VPNs are located at the following two locations:

       1. TLS Database Endpoint (TLS VPN):  TLS Database Endpoint 

       2. IPSEC VPN:  I need to check my VPN to the Vista Database? How do I do that? Is there a firewall blocking me?

    2. Set Up Dedicated SQL Account(s):  You will need to create one or more dedicated SQL accounts for SSMS.  One of your admin users with access to VA User Profile form will use the Vista application itself to create this account.  Vista will create the appropriate SQL account if you follow the subsequent instructions.  Normal Viewpoint cloud accounts do not work as they use AD, and your client workstations are not joined to Viewpoint's domain.

       1. Here is a link to the instructions on how to set up a SQL account in Vista:  I need a dedicated SQL account for my integration to Vista in your cloud. How do I set that up?

       2. NOTE:  You MUST grant your SQL service account(s) access to the appropriate data in Vista as necessary.  For SSMS access, you may need to place a support ticket with Viewpoint to ensure that this new SSMS account is granted DBO privileges.

       3. For example, if you have two users that access SSMS on-premise today, you'll need to create SQL accounts for these users to use with SSMS in our cloud.  Then copy their permissions to those new SQL accounts.

    3. IMPORTANT:  For security reasons you should use a separate SQL account for each end user and application that needs to have one; each human running SSMS should have their own, dedicated SQL account in the Vista Database for SSMS.

    4. Set up SSMS to talk to Vista:  Update the settings for SSMS to point to the new IP address for Vista's database in our cloud and use the new credentials created in the previous step.

       1.   If you have those, you can follow the instructions in the third party's help documentation to add a new database connection to enable the product to speak to the new Vista server.

    5. Open the Firewall Ports:  Ensure that you have opened the following ports in your firewall for the direct connection to and from Vista:

       1. TCP port specified in your TLS Database Endpoint (TLS VPN) (OR if using IPSEC please open 1433) inbound and outbound (this is the actual DB connection)

       2. UDP Port 1434 inbound and outbound (this is the SQL Server Browser service)

tl;dr:  Customers who need to integrate SSMS with Vista need to either host SSMS with us (RDP Cloud ONLY) or use the TLS Database Endpoint (TLS VPN) or IPSEC VPN and have SSMS installed locally (VRL Cloud).  If the customer is using VRL Cloud, they'll need to have a VPN set up to connect their network to the Viewpoint cloud and create a dedicated SQL account for each user.  Firewall ports will also need to be opened:  UDP port 1434 and TCP port 1433 for those leveraging the IPSEC VPN or the custom port for the TLS Database Endpoint (TLS VPN) if using that method.