With our Relational Data Service you can connect your own client-hosted, (external) databases to Emarsys and use the data fields contained in them to create segments or populate personalization variables. You can create multiple RDS connections even to the same database.
For general Relational Data information, see: Relational Data - Overview.
Preparing your database
Tables and views - When you connect an external database to Emarsys, we will automatically pull all the tables and views from the database and display them on the Relational Data page, Tables & Views tab. Tables and views are displayed only, if the schema is defined with the
CurrentSchemaconnection parameter. If it's omitted, the default schema is public. Ensure that your schema name is set correctly. For details, see Amazon Redshift.
Each table or view can only be used with one set of reference fields for personalization (which are linked by AND), therefore you need to create as many views as you will need in your database; however, you can create additional views at any time.
- Duplicates - Duplicate contacts are rejected by our connectors, so you will need to deduplicate your database before you start.
- Timezones - Make sure that in your database you specify the timestamp and date/time with the timezone, otherwise Emarsys applies CET (Europe/Vienna) timezone. You cannot define it as a Connection Parameter.
Securing connections: TLS/SSL
Please note that SSH Tunneling is not supported.
Emarsys supports server-side Transport Layer Security (TLS), successor of the Secure Sockets Layer (SSL) protocol. Server-side SSL is a requirement so Emarsys can guarantee secure server connection and provide encrypted communication. This is the same mechanism that is used to secure HTTPS traffic for websites all over the Internet. A trusted party “signs” the certificate of a server, ensuring authenticity and encryption. Trusted parties are identified by root certificates. For brief information on root certificates, click here.
Emarsys validates the server certificate against the certificate authority (CA) and checks the expiration date of both as well. With server certificate validation Emarsys ensures the authenticity of the database server and prevents domain thefts and person-in-the-middle attacks.
You can only connect to a server whose server certificate is signed by the CA that you configure on the Connection Details page (Add-ons -> Relational Data -> Connections tab and click Edit). It is the root CA certificate that is required here, not the SQL server certificate. If the server certificate was signed by an intermediate CA instead of the root CA directly, the entire CA chain must be pasted beginning with the root CA certificate in the CA Certificate text field. The whole certificate chain is always needed, but in most cases it is just a single root CA certificate.
On the client side, Emarsys currently only supports password authentication. To ensure a proper level of security, it is recommended to use strong passwords and change them frequently. Moreover, we establish connections from a fixed IP address (22.214.171.124), therefore, you can ensure proper security measures on the network level too.
Connection types with CA certificate
The following connection types require a CA certificate:
SSL configuration links
- MySQL: Configuring MySQL to Use Encrypted Connections
- PostgreSQL: Secure TCP/IP Connections with SSL
- Microsoft SQL: Enable Encrypted Connections to the Database Engine
- SAP HANA On-premise: Configure SSL for SAP HANA (CA)
CA certificate Format
The accepted format for your CA certificate is Privacy-Enhanced Mail (PEM).
In case of any certificate issue perform the following general troubleshooting steps:
- Generate a new server certificate and set the database to use the new certificate.
- Make sure that the whole certificate chain, beginning with the Root CA and ending one step short of the server certificate is pasted into the CA certificate text field.
- Check the expiration date of the server certificate and each CA certificate using the OpenSSL tool with the following command:
openssl x509 -in cert.pem -noout -text
- Click Test to test your connection when you have added all necessary data.
Before you start, note the following restrictions:
- Supported MySQL versions are 5.7 and 8.0.
- With Redshift and Snowflake connections, there is no need for a CA certificate.
- In order for us to have full access to your database, you need to add our IP: 126.96.36.199 to an allowlist.
The first step in using Relational Data is to set up the connection between your database and Emarsys.
To do this, go to Add-ons -> Relational Data -> Connections tab.
In the top right corner of the Connections tab, click the three dots and select the type of connection you would like to set up.
The following connection types are available:
The following connections are not supported for client-hosted databases:
- Azure Synapse
- Dynamo Db
- Hana S4 - Relational Data HANA Connector will only connect to the HANA DB, which means that any views in Hana S4 will need to be recreated in Emarsys. There is currently no CDS support that would enable Hana S4 views to be imported.
Connection setup methods
- Amazon Redshift - For more information, see Onboarding using client-hosted Amazon Redshift databases.
- PostgreSQL - For more information, see Onboarding using client-hosted PostgreSQL databases.
- Google BigQuery - For more information, see Onboarding using client-hosted Google BigQuery databases.
- Microsoft SQL - For more information, see Onboarding using client-hosted Microsoft SQL databases.
- MySQL - For more information, see Onboarding using client-hosted MySQL databases.
- Azure SQL - For more information, see Onboarding using client-hosted Azure SQL databases.
- Snowflake - For more information, see Onboarding using client-hosted Snowflake databases.
- SAP HANA Cloud - For more information, see Onboarding using client-hosted SAP HANA Cloud databases.
- SAP HANA On-Premise - For more information, see Onboarding using client-hosted SAP HANA On-Premise databases.