• Deutsch Español Français Русский Türkçe 简体中文
    1. Emarsys
    2. Getting Started
    3. Data Onboarding

    Relational Data onboarding for clients using client-hosted databases
    Updated

    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.  

    You can also upload your business-specific, relational data to Emarsys-hosted databases. For information, see Relational Data onboarding for clients using Emarsys-hosted databases.

    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 CurrentSchema connection 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

    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 (185.4.123.112), 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

    CA certificate Format

    The accepted format for your CA certificate is Privacy-Enhanced Mail (PEM).

    Troubleshooting

    In case of any certificate issue perform the following general troubleshooting steps:

    1. Generate a new server certificate and set the database to use the new certificate.
    2. 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.
    3. 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
    4. Click Test to test your connection when you have added all necessary data.

    Connection setup

    Before you start, note the following restrictions:

    • The supported mySQL versions are: 5.6, 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: 185.4.123.112 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:

    Which type of connection setup are you interested in?

    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.

    1. After clicking Create MySQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk:
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. Click Save to create your connector.

    In your MySQL database, the following roles must be assigned to the database user you share with Emarsys: SELECT, SHOW VIEW

    If you want to use MyAdmin, you should additionally assign the following roles to the database user you share with Emarsys: ALTER, CREATE, CREATE VIEW, DELETE, DROP, INSERT, INDEX, UPDATE

    Also, the following IPs need to be allowlisted:

    • 34.89.173.3
    • 35.246.249.205
    • 34.89.137.140
    MyAdmin

    The edit and delete options are only available with connections where the database is not hosted by Emarsys.

    The view option () is available only with MySQL connections, regardless of whether the database is external or internal (hosted by Emarsys). This option always takes you to the MyAdmin page, where your can make the necessary settings.

    The information and controls on the MyAdmin page are maintained by phpMyAdmin, not Emarsys. 

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Create Redshift connection you can set up your connection in the next window.
    2. You can create a single or even multiple RDS connections by adding your Amazon Redshift schema. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. You can define the schema name in the Connection parameters field, as follows: currentschema=<schema_name>.
      For public schemas, leave the field blank.
    4. Test your connection, when you have added all necessary data.
    5. Click Save to create your connector.

    In your Amazon Redshift database, the following role should be assigned to the database user you share with Emarsys: SELECT

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Create PostgreSQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host Id of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. Click Save to create your connector.

    In your PostgreSQL database, the following role should be assigned to the database user you share with Emarsys:

    • SELECT

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    The Google BigQuery database connector can not be used for personalization fields.
    It can be used to pull data into Emarsys for segmentation purposes only.

    This is because BigQuery is optimized for massive datasets and its queries take 2-5 seconds, regardless of the size of the dataset. Using it for personalization queries would have a negative impact on email sending performance.

    1. After clicking Create BigQuery connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Project ID – Enter the your project Id.
      • Dataset – Give a descriptive name to your database.
      • Client Email – Enter the email address of your client.
      • Private key – Enter your privatekey.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. Click Save to create your connector.

    In your Google BigQuery database, the following roles should be assigned to the database user you share with Emarsys: BigQuery Data Viewer, BigQuery Job User

    You can also import all the information in a JSON file directly from your Google Cloud Console. For detailed instructions on how to do this, click here.

    When you are done, you can now continue and create segments with Relational Data.

    1. After clicking MsSQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. Click Save to create your connector.

    In your Microsoft SQL database, the following privilege should be assigned to the Emarsys user:

    • SELECT

    To be able to use the Quick Test function of the the Segment Template Creator the SHOWPLAN privilege must be assigned to the Emarsys user.

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Azure SQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. Click Save to create your connector.

    In your Azure SQL database, the following roles should be assigned to the Emarsys user:

    • DELETE, INSERT, SELECT, UPDATE, VIEW DEFINITION
    azuresql emarsys

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    Before you start, note the following requirements:

    • Give the user USAGE privilege for the warehouse.
    • Give the user SELECT privilege for every table and view in the schema you would like to use for segmentation and personalization.

    Meeting these requirements ensures Emarsys' access to your database.

    1. After clicking Create Snowflake connection, you can set up your connection in the next window.

    You can create multiple connections.

    2. Make sure to fill in all fields marked with an asterisk. Every field is case-sensitive.

    • Account name - is the name assigned to your account by Snowflake. In the hostname you received from Snowflake (after your account was provisioned), your account name is the entire string to the left of snowflakecomputing.com. Example: xy12345.us-east-1-gov.aws.
    • Warehouse name - the name of the virtual warehouse existing in Snowflake.
    • Schema - the name of the schema you want us to use when we connect to snowflake. Make sure that the given user has access to the schema.
    • Database name  - the name of the database you want us to use when connecting.
    • User name - is the login name for the Snowflake user we will use when connecting.
    • Password - password for the Snowflake user we will use when connecting.

    3. Test your connection, when you have added all necessary data.

    4. Click Save to create your connector.

    After setting up your Snowflake connection, you are able to start segmenting using your data and personalize your content with Relational Data.

    Preparations

    To let Emarsys access your database, do the following on your database host:

    • Add the IP address 185.4.123.112 (Emarsys) to the relevant allowlist.
    • Grant the SELECT privilege to the user for the used schema.

    Setting up the connection

    1. Go to Add-ons → Relational Data → Connections on the Emarsys platform.
    2. Click Create SAP Hana Cloud connection to set up a connection of this type.
    3. Enter the details for a single or multiple connections to create.
      All fields are case-sensitive!
      • Instance ID: This is the ID of the instance that you want us to connect to. The ID contains 36 character digits, the word hana, subaccount name and an indication of where the database is hosted. You can find this in the Database overview in SAP Hana in the Host field. No need to copy the .hanacloud.ondemand.com section, this part is automatically added to all IDs. An example of what to copy: 5c30262b-2a57-46af-a535-27a0d88a2dc1.hana.trial-eu10.
      • User name: login name for the HANA user for this connection.
      • Password: password for the HANA user for this connection.

        Fields marked with an asterisk (*) on the interface are mandatory.
    4. When you finished adding all the necessary data, Test your connection.
    5. Click Save to create the connector you just set up.
    1. Once successfully saved, verify that Emarsys can access the right tables and views using the provided user by navigating to the Tables & Views tab.

    Virtual tables are not supported. We suggest connecting the other database also to Relational Data and using the view in this way, so better performance can be achieved.

    Preparations

    To let Emarsys access your database, do the following on your database host:

    • Add the IP address 185.4.123.112 (Emarsys) to the relevant allowlist.
    • Grant the SELECT privilege to the user for the used schema.

    Setting up the connection

    1. Go to Add-ons → Relational Data → Connections on the Emarsys platform.
    2. Click Create SAP Hana On-premise connection to set up a connection of this type.
    3. Enter the details for a single or multiple connections to create.
      All fields are case-sensitive!
      • Host: the host name of the database
      • Port: the port we can use to connect to the database
      • Databasse name: The name of the database
      • User name: the login name of the HANA user for this connection
      • Password: the password of the HANA user for this connection
      • CA Certificate: the root CA certificate
        If necessary, see further information on CA certificates and how to configure them for SAP HANA.
    4. When you finished adding all the necessary data, Test your connection.
    5. Click Save to create the connector you just set up.
    Congratulations, you are done!
    Was this article helpful?
    1 out of 2 found this helpful
    Return to top