This article addresses how Integrator can be used in an environment where the Sage 300 Construction and Real Estate software and Integrator software exist on the same network but must connect with a Prolog Manager database that is on a remote network such as a network connected via a Virtual Private Network (VPN) or a cloud-hosted environment, as illustrated here:
The guidelines covered in the Integrator Installation Guide still apply in this context. The guide addresses the prerequisite software that must be installed on any computer that will be running the Integrator software.
In the above scenario, the Integrator software would be installed on a machine that is running on the same network as the Sage 300 Construction and Real Estate accounting server. The database connection that Integrator makes to the accounting data will therefore be "local" meaning that it is reading and writing data on the same network. In addition, Integrator's licensing information and some basic configuration settings will reside on a machine on the local network. The unique aspect of the above configuration is that the Prolog Manager database is located on a machine that is not on the local network. This could be a VPN connection to a server that is running at another location or it could be a cloud-hosted server.
For the above scenario to work, Integrator must be able to connect to the Prolog Manager database using a standard Microsoft SQL Server ODBC connection. A VPN connection will normally provide this type of connectivity by default assuming that the TCP/IP port used by SQL Server (normally 1433) is available and DNS has been configured to allow the name of the SQL Server to be resolved.
In the case of a cloud-hosted server, additional configuration steps will often be needed in order to support a direct connection to the Prolog Manager database. Specifically, the port used by SQL Server (normally 1433) must be opened at the router. Integrator will then be able to make a connection to the SQL Server by identifying the server using it's public IP address (or by using a host name if a DNS entry has been made that maps to the IP address; e.g., "sqlserver.mydomain.com"). Note that this article is intended to only cover the basic requirements. Details pertaining to security, firewall configuration and DNS, would be the responsibility of a qualified I.T. professional.
Integrator Database Creation & User Management
The first time that Integrator establishes a connection to the SQL Server where the Prolog Manager database resides, three things occur:
- The Integrator is created
- A new login named "SDKUser" is created on the server
- The "SDKUser" login is associated as a user for the selected Prolog Manager database
For these tasks to be performed, the account used by Integrator to connect to the SQL Server must have sufficient rights create databases, logins, and users. This requirement can be satisfied by making this first connection using the 'sa' (System Administrator) account if available. If the 'sa' account is not available, the system administrator will need to create a login with the necessary permissions before attempting to make a connection with Integrator. Once the first connection has been made and the above tasks have been completed, a user account with fewer permissions can be utilized.
This article does not address the specific administrator tasks needed to grant the specific permissions needed. It is expected that a qualified database server administrator will be able to provide this.
It should be noted that the performance of the integration tasks is directly impacted by the speed of the connection to the remote server. Remote database connections are typically much slower than connections over a local network.