Database Security and Authentication

Whitelist IP Addresses

A Firewall will be protecting your Database Server in the majority of cases. This controls the things that can access it. Before attempting to connect to your Database Server you will need to whitelist the IP addresses and ports of the Sync Services.

Sync ServerIP Address
Sync Server 1100.100.100.100
Sync Server 2100.100.100.100
Sync Server 3100.100.100.100

Further lockdown access to the Database Server by restricting the Ports to only those needed. It is possible to specify the port used when setting up each Database connection. You can choose to use either the standard for the specific Database. Or you can choose your own if you wish to implement a layer of security by obscurity.

The table below lists standard ports for each type of Database Server.

DatabaseStandard Port
SQL Server / SQL Azure / SQL Data Warehouse1433

SSH Certificate supports using a SSH Tunnel to further protect your Database Server. This approach protects the Server behind an SSH Server which the Sync Servers must connect via. Authentication is handled by an encrypted public / private key pair. supports using SSH Tunnels on all database types. Shown below is an example of setting up a new PostgreSQL connection.

Database Login and Schema

The Sync Service needs credentials to login to your Database Server as well as access the Databases and Schema’s from which you would like to pull data.

We recommend that:

  • Created a database Login should be especially for Do not share an account with other Users or Services.
  • Restricted access and permissions as required. More on this below. As a minimum we recommend creating a Read-Only account. There is no requirement for the Sync Services to write or modify any data in your Databases.

Database Schema Access

When deciding which Database objects that the Sync Service needs access to it is worth considering how interrogates the Database schema. We ask that a series of Views are created to help structure the bespoke nature of data in Databases. These Views query the Database Tables and structure data in way compatible with the Sync Services.

Naming convention is used identify these Views to the Sync Services. For example v_distil_customers will be identified as containing Customer Data. v_distil_products will be identified as containing Product data. The Sync Service will ignore all other Tables and Views in the Database. The Database View Scripts section contains full details of this process.

As we are asking that you create these Views it is possible to choose to place them into their own Schema; distil_ai for example.

If you choose to do this, then you can further restrict access to the Database Login you have provisioned to just that schema. This not only provides an added layer of security, but also adds the benefit of limiting the number of object that needs to interrogate in your Database.

Our Supported Onboarding Service can assist you in connecting your Database to the Sync Service. We can also assist with creating Views described in the Database View Scripts section, should you need it.

Test Connection

Use the Test Connection button to ensure that you have successfully connected the Sync Services to your Database.

A green tick will confirm that the connection is working. Sync Servers will start looking for compatible Views, as described in Database Views. If any are found they will be listed in the appropriate column under Customer, Product and Purchase History data sets. These are described in Datasets.