Configuring FDA connectors

Depending on the external databases that you want to be able to access from Adobe Campaign, you will need to carry out certain specific configurations. These configurations essentially involve installing drivers and declaring environment variables that belong to each RDBMS on the Adobe Campaign server.

For more information on legacy connectors such as Teradata, Hadoop 2.1 or Netezza, refer to this page.

As a general rule, you need to install the corresponding client layer on the external database on the Adobe Campaign server.

NOTE

Compatible versions are listed in Campaign Compatibility Matrix.

Configure access to Azure Synapse

Azure synapse external account

The Azure external account allows you to connect your Campaign instance to your Azure Synapse external database.
To create your Azure Synapse external account external account:

  1. In Campaign Classic, configure your Azure Synapse external account. From the Explorer, click Administration / Platform / External accounts.

  2. Click New.

  3. Select External database as your external account’s Type.

  4. Configure the Azure Synapse external account, you must specify:

    • Type: Azure Synapse Analytics

    • Server: URL of the Azure Synapse server

    • Account: Name of the user

    • Password: User account password

    • Database: Name of the database

Azure Synapse on CentOS

Prerequisites:

  • You will need root privileges to install a ODBC driver.
  • Red Hat Enterprise ODBC drivers provided by Microsoft can also be used with CentOS to connect to SQL Server.
  • Version 13.0 will work with Red Hat 6 and 7.

To configure Azure Synapse on CentOS:

  1. First, install the ODBC Driver. You can find it in this page.

    NOTE

    This is exclusive to version 13 of the ODBC Driver.

    sudo su
    curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
    exit
    # Uninstall if already installed Unix ODBC driver
    sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
    
    sudo ACCEPT_EULA=Y yum install msodbcsql
    
    sudo ACCEPT_EULA=Y yum install mssql-tools
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
    source ~/.bashrc
    
    # the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1, so add soft links to the '.so.2' files
    cd /usr/lib64
    sudo ln -s libodbccr.so.2   libodbccr.so.1
    sudo ln -s libodbcinst.so.2 libodbcinst.so.1
    sudo ln -s libodbc.so.2     libodbc.so.1
    
    # Set the path for unixODBC
    export ODBCINI=/usr/local/etc/odbc.ini
    export ODBCSYSINI=/usr/local/etc
    source ~/.bashrc
    
    #Add a DSN information to /etc/odbc.ini
    sudo vi /etc/odbc.ini
    
    #Add the following:
    [Azure Synapse Analytics]
    Driver      = ODBC Driver 13 for SQL Server
    Description = Azure Synapse Analytics DSN
    Trace       = No
    Server      = [insert your server here]
    
  2. If needed, you can install unixODBC development headers by running the following command:

    sudo yum install unixODBC-devel
    
  3. After installing the drivers, you can test and verify your ODBC Driver and query your database if needed. Run the following command:

    /opt/mssql-tools/bin/sqlcmd -S yourServer -U yourUserName -P yourPassword -q "your query" # for example -q "select 1"
    
  4. In Campaign Classic, you can then configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

  5. Since Azure Synapse Analytics communicates through the TCP 1433 port, you need to open up this port on your firewall. Use the following command:

    firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="[server_ip_here]/32" port port="1433" protocol="tcp" accept'
    # you can ping your hostname and the ping command will translate the hostname to IP address which you can use here
    
    NOTE

    To allow communication from Azure Synapse Analytics’ side you might need to add your public IP to the allowlist. To do so, refer to Azure documentation.

  6. In case of iptables, run the following command:

    iptables -A OUTPUT -p tcp -d [server_hostname_here] --dport 1433 -j ACCEPT
    

Azure Synapse on Windows

NOTE

This is exclusive to version 13 of the ODBC Driver but Adobe Campaign Classic can also use SQL Server Native Client drivers 11.0 and 10.0.

To configure Azure Synapse on Windows:

  1. First, install the Microsoft ODBC driver. You can find it in this page.

  2. Choose the following files to install:

    your_language\your_architecture\msodbcsql.msi (i.e: English\X64\msodbcsql.msi)
    
  3. Once your ODBC driver is installed, you can test it if needed. For more on this, refer to this page.

  4. In Campaign Classic, you can then configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

  5. Since Azure Synapse Analytics communicates through the TCP 1433 port, you need to open up this port on the Windows Defender Firewall. For more on this, refer to Windows documentation.

Azure Synapse on Debian

Prerequisites:

  • You will need root privileges to install a ODBC driver.

  • Curl is needed to install the msodbcsql package. If you don’t have it installed, run the following command:

    sudo apt-get install curl
    

To configure Azure Synapse on Debian:

  1. First, install the Microsoft ODBC driver for SQL Server. Use the following commands to install the ODBC Driver 13.1 for SQL Server:

    sudo su
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
    curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list
    exit
    sudo apt-get update
    sudo ACCEPT_EULA=Y apt-get install msodbcsql
    
  2. If you get the following an error “The method driver /usr/lib/apt/methods/https could not be found” when calling sudo apt-get update, you should run the command:

    sudo apt-get install apt-transport-https ca-certificates
    
  3. You now need to install mssql-tools with the following commands. Mssq-tools are needed to use the bulk copy program (or BCP) utility and to run queries.

    sudo ACCEPT_EULA=Y apt-get install mssql-tools
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
    source ~/.bashrc
    
  4. If needed, you can install unixODBC development headers by running the following command:

    sudo yum install unixODBC-devel
    
  5. After installing the drivers, you can test and verify your ODBC Driver and query your database if needed. Run the following command:

    /opt/mssql-tools/bin/sqlcmd -S yourServer -U yourUserName -P yourPassword -q "your query" # for example -q "select 1"
    
  6. In Campaign Classic, you can now configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

  7. To configure iptables on Debian to ensure the connection with Azure Synapse Analytics, enable the outbound TCP 1433 port for your hostname with the following command:

    iptables -A OUTPUT -p tcp -d [server_hostname_here] --dport 1433 -j ACCEPT
    
    NOTE

    To allow communication from Azure Synapse Analytics’ side you might need to add your public IP to the allowlist. To do so, refer to Azure documentation.

Configure access to Snowflake

NOTE

Snowflake connector is available for hosted and on-premise deployments. For more on this, refer to this page.

Snowflake external account

The Snowflake external account allows you to connect your Campaign instance to your Snowflake external database.

  1. In Campaign Classic, configure your Snowflake external account. From the Explorer, click Administration / Platform / External accounts.

  2. Click New.

  3. Select External database as your external account’s Type.

  4. Configure the Snowflake external account, you must specify:

    • Type: Snowflake

    • Server: URL of the Snowflake server

    • Account: Name of the user

    • Password: User account password

    • Database: Name of the database

  5. Click the Parameters tab then the Deploy functions button to create functions.

The connector supports the following options:

Option Description
workschema Database schema to use for work tables
warehouse Name of the default warehouse to use. It will override the user’s default.
TimeZoneName By default empty, which means that the system time zone of the Campaign Classic app server is used. The option can be used to force the TIMEZONE session parameter.
For more on this, refer to this page.
WeekStart WEEK_START session parameter. By default set to 0.
For more on this, refer to this page.
UseCachedResult USE_CACHED_RESULTS session parameter. By default set to TRUE. This option can be used to disable Snowflake cached results.
For more on this, refer to this page.

Snowflake on CentOS

  1. Download the ODBC drivers for Snowflake. Click here to start downloading.

  2. You then need to install the ODBC drivers on CentOs with the following command:

    rpm -Uvh unixodbc
    rpm -Uvh snowflake-odbc-2.20.2.x86_64.rpm
    
  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:

    /etc/init.d/nlserver6 stop
    /etc/init.d/nlserver6 start
    
  4. In Campaign Classic, you can then configure your Snowflake external account. For more on how to configure your external account, refer to this section.

Snowflake on Debian

  1. Download the ODBC drivers for Snowflake. Click here start downloading.

  2. You then need to install the ODBC drivers on Debian with the following command:

    apt-get install unixodbc
    apt-get install snowflake-odbc-x.xx.x.x86_64.deb
    
  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:

    systemctl stop nlserver.service
    systemctl start nlserver.service
    
  4. In Campaign Classic, you can then configure your Snowflake external account. For more on how to configure your external account, refer to this section.

Snowflake on Windows

  1. Download the ODBC driver for Windows. Note that you need administrator-level privileges to install the driver. For more on this, refer to this page

  2. Configure the ODBC driver. For more on this, refer to this page

  3. In Campaign Classic, you can then configure your Snowflake external account. For more on how to configure your external account, refer to this section.

Configure access to Hadoop 3.0

Hadoop external account

The Hadoop external account allows you to connect your Campaign instance to your Hadoop external database.

  1. In Campaign Classic, configure your Hadoop external account. From the Explorer, click Administration / Platform / External accounts.

  2. Click New.

  3. Select External database as your external account’s Type.

  4. Configure the Hadoop external account, you must specify:

    • Type: ODBC (Sybase ASE, Sybase IQ)

    • Server: Name of the DNS

    • Account: Name of the user

    • Password: User account password

    • Database: Name of your database if not specified in DSN. It can be left empty if specified in the DSN

    • Time zone: Server time zone

The connector supports the following ODBC options:

Name Value
ODBCMgr iODBC
warehouse 1/2/4

The connector also supports the following Hive options:

Name Value Description
bulkKey Azure blob or DataLake access key For wasb:// or wasbs:// bulk loaders (i.e. if the bulk load tool starts with wasb:// or wasbs://).
It is the access key for blob or DataLake bucket for bulk load.
hdfsPort port number
set by default to 8020
For HDFS bulk load (i.e. if the bulk load tool starts with webhdfs:// or webhdfss://).
bucketsNumber 20 Number of buckets when creating a clustered table.
fileFormat PARQUET Default file format for work tables.

Configuring Hadoop 3.0

Connecting to a Hadoop external database in FDA requires the following configurations on the Adobe Campaign server. Note that this configuration is available for both Windows and Linux.

  1. Download the ODBC drivers for Hadoop depending on your OS version. Drivers can be found on this page.

  2. You then need to install the ODBC drivers and create a DSN for your Hive connection. Instructions can be found in this page

  3. After downloading and installing the ODBC drivers, you need to restart Campaign Classic. To do so, run the following command:

    systemctl stop nlserver.service
    systemctl start nlserver.service
    
  4. In Campaign Classic, you can then configure your Hadoop external account. For more on how to configure your external account, refer to this section.

Configure access to Oracle

Oracle external account

The Oracle external account allows you to connect your Campaign instance to your Hadoop external database.

  1. In Campaign Classic, configure your oracle external account. From the Explorer, click Administration / Platform / External accounts.

  2. Click New.

  3. Select External database as your external account’s Type.

  4. Configure the Oracle external account, you must specify:

    • Type: Oracle

    • Server: Name of the DNS

    • Account: Name of the user

    • Password: User account password

    • Time zone: Server time zone

Oracle on Linux

Connecting to an Oracle external database in FDA requires additional configurations below on the Adobe Campaign server.

  1. Install the Oracle full client corresponding to your version of Oracle.

  2. Add your TNS definitions to your installation. To do this, specify them in a tnsnames.ora file in the /etc/oracle repository. If this repository does not exist, create it.

    Then create a new TNS_ADMIN environment variable: export TNS_ADMIN=/etc/oracle and restart the machine.

  3. Integrate Oracle into your Adobe Campaign server (nlserver). To do this, check that the customer.sh file is present in the “nl6” folder of the Adobe Campaign server tree structure and that it includes the links to the Oracle libraries.

    For example, for a client in 11.2:

    export ORACLE_HOME=/usr/lib/oracle/11.2
    export TNS_ADMIN=/etc/oracle
    export LD_LIBRARY_PATH=$ORACLE_HOME/client64/lib:$LD_LIBRARY_PATH
    
    NOTE

    These values (particularly ORACLE_HOME), depends on your installation repositories. Make sure to check your tree structure before referencing these values.

  4. Install the libraries necessary for Oracle:

    • libclntsh.so

      cd /usr/lib/oracle/<version>/client<architecture>/lib
      ln -s libclntsh.so.<version> libclntsh.so
      
    • libaio1

      aptitude install libaio1
      or
      yum install libaio1
      
  5. In Campaign Classic, you can then configure your Oracle external account. For more on how to configure your external account, refer to this section.

Oracle on Windows

Connecting to an Oracle external database in FDA requires additional configurations below on the Adobe Campaign server.

  1. Install the Oracle client.

  2. In the C:Oracle folder, create a tnsnames.ora file containing your TNS definition.

  3. Add a TNS_ADMIN environment variable with C:Oracle as value and restart the machine.

  4. In Campaign Classic, you can then configure your Oracle external account. For more on how to configure your external account, refer to this section.

On this page