With Relational Data you can connect your own databases to Emarsys using one of our supported connectors and use the available data to create segments or populate personalization variables.
The following databases are supported by Emarsys Relational Data. If the database you want to use is not listed then it is not supported.
- Amazon Redshift
- Azure SQL
- Google BigQuery
- Microsoft SQL
- MySQL
- PostgreSQL
- SAP HANA Cloud
- SAP HANA On-Premise
- Snowflake
For general Relational Data information, see: Relational Data - Overview.
Preparing your database
Before connecting your database to Relational Data make sure that everything is configured to work with Emarsys correctly.
- 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.
-
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.
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.
Make sure you do not use the space character in the names of tables or views. Use underscore, for example, instead. If you use space, you get template compile errors and your table or view will just not work. While my_new_rds_table is good as a name, my new rds table fails.
If your database is set up to not store the data in a public schema then you need to define the currentSchema connection parameter, otherwise the tables and views will not work. If you do not have a specific schema configured then simply leave the currentSchema connection parameter empty to use the public schema.
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.
We 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.
Emarsys establishes connections from a fixed IP address, therefore, you can ensure proper security measures on the network level too.
- Add the following IP addresses to the relevant allowlist:
34.89.173.3,34.89.137.140,35.246.249.205,185.4.123.112
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). Databases hosted and managed by cloud providers like HANA Cloud, Azure, Redshift, or Snowflake do not need to have a CA certificate specified.
Troubleshooting
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.
Connection setup methods
Connect your database using any of the following connectors: