The simplest way to allow AppSheet to connect to an on-premises database is to open up the database's firewall to AppSheet servers' IP addresses. See Manage IP address and firewall information for a list of AppSheet's IP addresses. The main advantage of this method is that it is quick and easy to implement. However, opening the firewalls of an on-premises database is considered an unsafe practice by many. To address this problem, AppSheet now supports DreamFactory, which allows Appsheet to securely connect to on-premise databases.
Install DreamFactory
DreamFactory is a proxy server that exposes a REST API through which third-party applications can securely connect to your databases.
There are two ways to install DreamFactory. The first method is to create a DreamFactory instance hosted on a web server that is separate from your database's server. In this case, you will still need to open your database's firewall to allow the DreamFactory instance to connect to your database. The second approach is to download and install DreamFactory into your on-premise server alongside your database. See DreamFactory for more information on how to install and set up DreamFactory.
Register AppSheet with DreamFactory
Once you have set up DreamFactory, log into your DreamFactory instance as an admin, and open the Apps tab, and register AppSheet by clicking Create. Name the new app AppSheet, check the Active check box, select On a remote URL as the app's location, and enter AppSheet's URL (https://appsheet.com/).
After you click save to register AppSheet, DreamFactory will automatically generate a new API key that AppSheet can use to connect to your DreamFactory instance.
Register your database as a new service
To allow DreamFactory to connect to your database, you need to register it as a new service in your DreamFactory instance.
In DreamFactory, open the Services tab, and click Create to add a new service. In the Info sub-tab, select the appropriate service type based on the type of your database. Currently, AppSheet only supports SQL databases (SQL Server, MySQL, MariaDB, and PostgreSQL). For MariaDB databases, you can select MySQL. After selecting the service type, give your service a name and a label.
Next, open the Config sub-tab and fill out the address of your database server, the database's name, the username, and the password. If your database does not use the default port, you will need to specify the port number as well. If your DreamFactory instance is not hosted on the same server as your database, you will also need to note down the instance's IP address in the Config sub-tab and add it to your database's firewall. Most of the other settings can be ignored.
Once you have saved your new service, open the Schema tab to verify that DreamFactory can now connect to your database. The newly created service should now be available in the service drop-down menu. Select the new service and then open the Table drop-down menu. If your service has been configured correctly, DreamFactory should be able to populate the drop-down menu with the names of tables and views in the database. Note that you may need to click Refresh for the tables and views to appear.
Create roles and users
For security reasons, AppSheet can only connect to your DreamFactory instance using a user account instead of an admin account. As a result, you will need to create a new DreamFactory user account for AppSheet, and assign the appropriate role to this account.
First, open the Roles tab and click Create to create a new role. In the Basic sub-tab, name the new role and check the Active checkbox.
Next, open the Access sub-tab and click on the plus button to add two new service access rules.
For the first rule, select the database service that you have created, choose the component _schema/* , and enable the GET access mode. This access rule will allow AppSheet to access the table schemas in your database in order to obtain and analyze the column structure of your tables and views. Since AppSheet only needs to read the table schemas without making any modification, only the GET access mode needs to be enabled.
For the second rule, choose the component _table/* , and enable all available access modes. This rule will allow AppSheet to read and write data to tables and views in your database.
After you have saved the new role, open the Users tab and click Create to add a new user account. In the Basic sub-tab, fill out the email address of the account, check both the Active and Set Password checkboxes, and create a new password for the account.
Next, open the Roles sub-tab. If AppSheet has been correctly registered in your DreamFactory instance, you should see AppSheet listed as one of the apps available to the new account. Select the role that you have just created for AppSheet, and the save the user account.
Add DreamFactory as a data source in AppSheet
Once you have set up and configured you DreamFactory instance, you can add the instance as a data source to your AppSheet account. To do this, open the Sources tab in the account page, click the add data source button, name your new data source, and then select On-premises Database as the data source type.
In the DreamFactory connection information page, fill out the following fields:
- Database type: the type of your database (MySQL, SQL Server, etc.)
- Server: the address of your DreamFactory instance. This must be a publicly accessible URL, IP address, or host name.
- API key: the API key assigned to AppSheet by DreamFactory
- Service name: the service name of your database in DreamFactory. Note that this is not the application name that you used to register AppSheet with DreamFactory.
- User email: the email of the DreamFactory user account that you have created
- Password: the password of the user account
Once the above fields have been filled out, test the connection, and finally authorize AppSheet to access your database through DreamFactory.