CONN Cloud Getting Started: Gateway Connector

Summary:

This article details how the Solver Gateway allows Solver Cloud to connect to an organization's on premise system. As a Cloud customer, you only get Gateway with file import as part of your purchase. An organization purchases the Gateway, which comes with a SQL and CSV/Excel connector, allowing Solver Cloud to see either one of these data sources and upload its content to Solver Cloud. The Gateway creates a secured connection between the organization's network and Solver Cloud without the organization having to open any network ports. This article will detail the configuration of the Gateway application.

Note: This KB article does not discuss the implementation of Gateway SQL views. For a step by step walkthrough of Gateway views implementation, please refer to the Solver Academy courses for additional information.

Article Sections:

• More Information
  • System Requirements
  • FAQ
  • Installation
  • Upgrades
  • Configuring the Gateway
  • Setting up the File Import Connector
  • Troubleshooting
• Properties

More Information:

Gateway version 2.2.1+ introduces a new CSV connector (File Import connector). Your existing CSV integrations will continue to work, but new CSV integrations must be built using the File Import connector. 

Note: the information discussed in this article is relevant to Solver Cloud customers. Gateway is not available for Private Host customers. 

Important items to note: 

• The Solver Gateway can only connect to one tenant. Organizations with two tenants must have two separate servers to host each install of the Gateway. 
• The machine on which the Gateway is installed must be running in order for the Gateway to communicate with Solver Cloud.
• It is recommended to have the Gateway installed on a standalone machine, depending on the size of the data the organization is loading into Solver Cloud, to avoid competing resources with other processes (i.e. SQL server). If Gateway is installed on a shared server and integration begins failing consistently, Gateway will need to be moved to a dedicated server. 
• The Excel connector has the following known issues in this release:
  • Files with special characters will not load. At this time, please avoid using special characters.
  • CSV and Excel files with the same name will result in error. Please keep file names unique in the folder the Gateway is referencing. 
  • Leading or trailing spaces in the column headers will result in preview being blank.
• If you plan to install the Gateway on a new server, be sure to either:
  • Uninstall the Gateway application from the old server.
  • Turn off the Solver “Gateway On-Prem” service from your computer’s Services menu.
• Special configuration is required for organizations using a proxy, click here. 

System Requirements

*SQL Express is supported, note that it may be slow from an upload speed perspective. 

Frequently Asked Questions

1. Can the Solver Gateway be installed on multiple computers? 
   • The gateway can be installed on only one computer per tenant. We recommend that it is on a server and a service account is used. 
2. I have multiple databases located on separate SQL servers that, I'd like to connect Solver Cloud to, is this doable? 
   • As mentioned in #1, the Gateway can be installed on only one computer. The Gateway CAN connect to multiple servers as long as the computer the Gateway is installed on has access to all SQL servers.
3. What authentication methods to the database are supported?
   • The Gateway supports Windows or SQL authentication.
4. Will Excel files be supported? 
   • The Gateway supports both Excel and .CSV files (.csv, .xls, .xlsx, .xlsm).
5. Can you explain the details of how the Gateway connects to our organization.
   • Solver Cloud utilizes HTTPS/TLS1.2 for all communication and the Gateway inherits the same communication protocol via port 443. Communication is established by the Gateway and persists via a technology called SignalR. This initiation will begin a long polling (Specifically Server Sent Events (SSE) connection between Solver Cloud and the Gateway on premises service. Because the Gateway is always installed locally, behind a firewall, there is no inbound communication possible.

Installation

The next section will detail installations and upgrades. Please reference the section that pertains to your installation. 

New Installs

Installation of the Solver Gateway is simple and easy. To do so, follow these steps:

1. Begin by downloading the Gateway from the Gateway Setup Resources Link within the Gateway Connector found in the Solver Marketplace. This link will also give you an access token. Copy this token now, we will need it later. 
   
2. Make sure the Gateway installer is on the server where the Gateway will be installed. 
3. Run the setup file as an Admin (Right click > Run as Admin). If a UAC popup appears, go ahead and click Yes.
4. In the popup that appears, review the License terms and click the checkbox next to “I agree to the License terms and conditions“. This will enable the Install button. 
5. Click Install.
6. The install will begin and usually takes less than a minute.
7. Assuming the installation is successful, a popup will appear to notify you of a completed install. Click Finish. 
8. This completes the installation of the Solver Gateway.

Upon clicking Finish, open your web browser and navigate to http://localhost:8080. The Solver Gateway landing page will appear. You have successfully installed the Gateway and are ready to create a connection to your on-premise data source and on CSV/Excel files. 

Should there be any issues with the installation, please retry the installer. If it fails again, please contact Solver Support (support@solverglobal.com).

Upgrades

There are different methodologies depending on the version of Gateway installed:

• For customers on Gateway version 0.x, please contact Solver Support. 
• For customers on Gateway version 1.x, you must manually uninstall the Gateway from the Control Panel first.
• For customers on Gateway version 2.x, you can simply run the new Gateway installer (v 2.2.1) and it will automatically update your Gateway application. 

Upgrading to the Latest Gateway version from Gateway 1.x

Before beginning, take note of the following as you will be creating a new connection.

1. The current server information where the current Gateway is installed. This can be found by going to http://localhost:8080 on your Gateway server.
2. Take note of the name of the Gateway connection created. 
3. Take a backup (copy) of the sqllite file found in C:\ProgramData\Solver|Gateway On-Premise\DB: gatewaydb.sqllite. 

The following should be done during a scheduled maintenance period. All integrations run during this upgrade period will not complete successfully. Follow these steps:

1. Begin by uninstalling the previous version of the Gateway from the Control Panel.
2. Once the uninstall is complete, run the new Gateway installer. Follow the instructions above. 
3. Once the installation is complete, restore the sqllite file in the Program Data > Solver > Gateway On-Premise > DB folder
4. Navigate to http://localhost:8080 from your web browser to access the new Gateway app

You have completed the upgrade.

Configuring the Gateway

Now with the Gateway up and running, follow these steps:

1. Click Start Connecting from the home page of the Gateway. 
2. You will be prompted for the access token which was available in the Gateway Setup Resource link.
3. Paste this code into the Access Token text box.
4. Assuming a valid access token has been provided, you will be taken to the Gateway welcome page where you can choose to connect to a SQL data source or a file import source (such as Excel or CSV). 

Some informative links on this page include:

• Template views of common data sources available on the left hand navigation pane.
• Organizations using a proxy server can click the Gear icon in the bottom left hand corner to configure the Gateway to work with an organization’s proxy. 

Setting up the File Import connector

1. Click the Add option within the File Import – Excel and CSV connector. 
   
   
   
   • Name Your Connection: this will be the name of the connection that you choose from within Solver Cloud. It is best to make this name descriptive to include information such as the computer name where the gateway is installed. 
   • Find Folder Path: Fill this information out by either typing in the name of a local drive or using the “Find Fold Path” to locate the folder containing files to be imported.
     • The folder path feature only supports local computer drives, it does not support network locations.
     • When navigating through the local drives, double-click to expand the drives. 
   • Indicate whether or not the file has column headers. Fill out the information requested.
2. Click Connect.
3. The application will scan the location pulling in all relevant CSV and Excel files.
   • Depending on the number of files, it may take some time to load. Each Excel file is opened to populate sheet names within the application.
   • For performance purposes, it is best to reference a folder that contains only the files that will be imported.
4. You have now successfully set up a connection to your Excel/CSV folder location and are ready to map your integration and begin loading data into Solver Cloud. 

NOTE: You cannot connect to a folder if any of the files are open.

Troubleshooting

For instructions on how to troubleshoot Gateway errors, please refer to this article.  

Properties:

Cloud