Configuring the SQL Service to connect to an On-Premises SQL database

If you wish to connect to an on-premises SQL database from Cloud, your database must be accessible over the internet. There are many ways to secure your On-Premises SQL server Database. It is out of scope for the purpose of this document to describe those methods, but please refer to Technet documentation and other sources on the internet, such as the following resources:

If you wish to limit connections to your on-premises from a specific IP address (which is the recommended approach for security), the IP address of an Cloud environment is available through the K2 Management . This IP address remains static throughout the subscription period, unless an underlying infrastructure maintenance causes it to change. You will be notified of such maintenance and appropriate action need to be taken to update your firewall with the new IP address.

After you have configured your On-Premises SQL server to be accessed over the internet, please verify that you can connect to it from external location through SQL Server Management Studio using SQL Server Authentication. Your OnPrem SQL server should be enabled for Mixed Mode Authentication (see https://msdn.microsoft.com/en-us/library/ms144284(v=sql.110).aspx). Once connection is verified through SSMS, navigate to K2 Management site and access the Service Instances node. Click on Add to configure a new SQL server service instance and use the information in the topic Service Keys (Service Instance Configuration Settings) for guidance. In the Service Keys section, make sure you change the highlighted values according to your OnPrem SQL server environment.

For the Server field, you can use one of the following options:

DNS - A record that points to your public facing SQL server IP address (e.g. sqlonprem01.Cloudlab.com)
An IP address (e.g. 123.45.67.89)
A record or IP address connecting on a specific port. If you don’t specify a port, the service instance will try to connect on the default SQL server port which is 1433 (e.g. sqlonprem01.cloudlab.com,25603 or 123.45.67.89,25603)

Static is the only allowed Authentication mode when connecting Cloud to an on-premises SQL database, since Windows Authentication cannot be used over the internet.

When connecting to on-premises systems from your K2 Cloud environment, you must use static credentials. See KB002939: Connecting to On-Premises Data from K2 Cloud for more information about how to do this for supported systems.

After the Service Instance is registered and SmartObjects are generated or manually created, you will be able to use those SmartObjects to access your LOB data from your On-Premises SQL server database.