Linux WBE Installation

This installation manual guides you through the installation of the Write Back Extreme extension on Linux.

Skill level

To follow along, knowledge of installing a webserver on Linux is required.

Operating System

This manual is written for Linux Ubuntu LTS 20.04.

Scope

The following topics will be outside the scope of this manual:

Configuring the firewall
- Webserver: 80/443
- MySQL: 3306
- MSSQL: 1433

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

1. Preparation

For the purpose of this manual, you should use the bash terminal with sudo privileges:

Copy to Clipboard
Syntax Highlightersudo -i

Create the installation folder for the extension:

Copy to Clipboard
Syntax Highlightermkdir -p /var/www/<write-back.company.com>/

Make sure that we use the correct OS (Ubuntu or other LTS system):

Copy to Clipboard
Syntax Highlighterlsb_release -a

Update dependency packages:

Copy to Clipboard
Syntax Highlighterapt update

2. Installing a webserver

In order to use Write Back Extreme, you need to run a webserver. Our recommendation for a webserver is NGINX, due to its lightweight and static file performance. Alternatively, you can install Apache2 or others depending on the operating system. Select your preferred webserver below and follow te installation steps:

NGINX
Apache2

NGINX

Install NGINX

This page will guide you through the installation of NGINX on Linux. This webserver is one of the options you have for using Write Back Extreme. Remove Apache2 if installed and not needed:

Copy to Clipboard
Syntax Highlighterapt remove apache2

Install NGINX via apt-get:

Copy to Clipboard
Syntax Highlighter# install NGINX and MySQL server 5.7 or 8 apt -y install nginx # check if running systemctl status nginx

Follow the steps for configuration and further installation.

Please use this link to download a pre-configured set of configuration files. Change the following parameters in the pre-configuration to your configuration:

Server -> Domain;
Server -> Path;
HTTPS -> SSL certificate and key
NGINX -> user: please check if this user is the same as your user.

Download config and follow instructions to place files in /etc/nginx/

Install SSL certificates in the right place

SSL init:

Copy to Clipboard
Syntax Highlighteropenssl dhparam -out /etc/nginx/dhparam.pem 2048

Restart NGINX

Copy to Clipboard
Syntax Highlighternginx -t && systemctl reload nginx

Apache2

Install Apache2/PHP Apache2

For installation of Apache2, please follow the guides provided at:

Note: installation fo different versions of Ubuntu may vary. Choose the version that’s right for your environment.

Note 2: Make sure to enable mod_rewrite in Apache2! You can do this as follows:

Copy to Clipboard

3. Installing PHP 7.4.15 or higher

To use Write Back Extreme you need to setup PHP correctly. We’d recommend installing PHP-FPM because it’s a standalone PHP instance. Other PHP instances require and run-only with Apache2. Check what version of PHP is currently installed:

Copy to Clipboard
Syntax Highlighter# Checking version of PHP, it should be 7.4 php -v

Install PHP

Add the following settings to php.ini to be able to upload extension-upgrade files:

Copy to Clipboard
Syntax Highlighter# Add PHP 7.4 repo dependencies apt -y install software-properties-common add-apt-repository ppa:ondrej/nginx-mainline add-apt-repository ppa:ondrej/php # Update the repositories with latest apt-get update apt -y install php7.4-fpm unixodbc # Install PHP extensions apt -y install php7.4-{bcmath,bz2,intl,gd,mbstring,mysql,zip,xml,odbc,curl} # check if php is running systemctl status php7.4-fpm

Restart PHP

Copy to Clipboard
Syntax Highlightersystemctl reload php7.4-fpm

4. Installing a repository database

The next step in installing Write Back Extreme is to install a database. As of this moment, we fully support 4 databases for system tables and schema creation. Other databases support schema-only. Supported databases are listed below. Follow the appropriate guide for the preferred system database. Afterwards you can install any of the database drivers for schema creation.

Repository Databases:

MySQL
SQL Server
PostgreSQL
SQLite

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

MySQL/MariaDB

Install MySQL/MariaDB

This guide will explain how to install either MySQL or MariaDB. Because installation for both is similar, these two database drivers are both described on this page. Please follow along using the parts of this guide that apply to the driver of your choice.

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

Install MySQL

Check which version is currently installed in your environment:

Copy to Clipboard
Syntax Highlighter# check version (should be 5.7 or above) mysql --version

Install MySQL 8:

Copy to Clipboard
Syntax Highlighter# back to home cd # get latest apt-config information for MySQL 8 wget -c https://repo.mysql.com//mysql-apt-config_0.8.16-1_all.deb; dpkg -i mysql-apt-config_0.8.16-1_all.deb ### SELECT: MySQL Server & Cluster ### SELECT: mysql-8.0 ### SELECT: Ok # install MySQL server 8 apt update apt -y install mysql-server ### CREATE: root password ### SELECT: Use Strong Password Encryption

Configure MySQL

Some configuration is optional for security reasons:

Copy to Clipboard
Syntax Highlighter# (MySQL 8 only) make sure the native password module is changed; # otherwise we cannot connect with username/password to database #### add to /etc/mysql/conf.d/mysql.cnf [mysqld] # Allow only local access bind-address = 127.0.0.1 # Disable LOCAL INFILE local-infile=0 # restart mysql systemctl restart mysql.service # configure mysql mysql_secure_installation ### validate password plugin: yes ### remove anonymous users: yes ### disallow root login remotely: yes ### remote test database: yes ### reload privilege tables now: yes # Restart MySQL server for applying configuration and user. systemctl restart mysql.service

Install MariaDB

To install MariaDB, please follow the default installation guide.

Configure MariaDB

Some configuration is optional for security reasons:

Copy to Clipboard
Syntax Highlighter# (MariaDB only) make sure the native password module is changed; # otherwise we cannot connect with username/password to database #### add to /etc/mysql/conf.d/mysql.cnf [mysqld] default-authentication-plugin=mysql_native_password # Allow only local access bind-address = 127.0.0.1 # Disable LOCAL INFILE local-infile=0 # restart mysql systemctl restart mysql.service # configure mysql mysql_secure_installation ### validate password plugin: yes ### remove anonymous users: yes ### disallow root login remotely: yes ### remote test database: yes ### reload privilege tables now: yes # Restart MySQL server for applying configuration and user. systemctl restart mysql.service

MySQL and MariaDB – Create database users

We’d highly recommend creating a read and write user for the database. Please do not use a period “.” in your passwords since this might break connections. The read-only user will be used for fetching and writing data into the table. The write/read user will be used by the system for managing the schemas and system tables in the database.

Copy to Clipboard
Syntax Highlighter# log in to mysql mysql -u root -p > CREATE DATABASE writeback_db; > CREATE USER 'writeback_owner'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; > GRANT ALL ON writeback_db.* TO 'writeback_owner'@'localhost'; > CREATE USER 'writeback_reader'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; > GRANT SELECT ON writeback_db.* TO 'writeback_reader'@'%'; > EXIT;

MS SQL Server

Install MS SQL

For other Linux installations, see Microsoft.com.

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

Install dev tools

Copy to Clipboard
Syntax Highlighterapt -y install php-pear php7.4-dev php7.4-sybase

ODBC driver

Copy to Clipboard
Syntax Highlightercurl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - # Ubuntu 20.04 (other versions > Ubuntu) curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list apt update ACCEPT_EULA=Y apt -y install msodbcsql17 unixodbc-dev

PHP extension

Copy to Clipboard
Syntax Highlighterpecl channel-update pecl.php.net # install driver pecl config-set php_ini /etc/php/7.4/fpm/php.ini pecl install sqlsrv pdo_sqlsrv printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.4/mods-available/sqlsrv.ini printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.4/mods-available/pdo_sqlsrv.ini phpenmod -v 7.4 sqlsrv pdo_sqlsrv

PostgreSQL/Redshift

Install PostgreSQL

We are currently working on an installation guide for PostgreSQL. Until this page gets updated, please follow the links provided below for installation instructions:

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

SQLite

Install SQLite

We do not recommend using SQLite. However, it is supported by Write Back Extreme. For an installation guide, please follow the documentation.

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

Snowflake

Install Snowflake

Snowflake cannot be used for initial install. However, you can install it later to enable schemas and write back actions.

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

Currently, Write Back Extreme is only supported via ODBC. Make sure you have installed the PHP ODBC extension via PECL. You should have already done this in step 3: Installing PHP:

Copy to Clipboard
Syntax Highlighterpecl install PDO_ODBC

Configure ODBC for Snowflake

Please follow this guide to configure the ODBC driver for Snowflake:

ODBC Setup

For Snowflake, we recommend you to setup the majority of the connection settings within the ODBC manager. The connection can be configured as system- or user DSN. There are some configurations required within the backend:

DSN name
Username
Password
Database

ODBC manager fields are leading and will overtake the “local” Write Back configuration. Some default configuration layout:

Copy to Clipboard
Syntax Highlighter[ODBC Data Sources] SnowflakeDSII = Snowflake [ODBC] Trace = 0 TraceAutoStop = 0 TraceFile = TraceLibrary = [SnowflakeDSII] Driver = {…Path to the driver…} Description = Snowflake DSII Server = customer.snowflakecomputing.com Port = 443 UID = customer Schema = PUBLIC Warehouse = ANALYTICS_WH Locale = en-US Tracing = 0 Database = WRITEBACK_DEMO

DSN name to use in WriteBack: SnowflakeDSII

OracleDB

Install OracleDB

Please follow this guide: https://gist.github.com/eSkiSo/781269c79b4dd740e90fcc059c1985ae

Points of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

5. Installing and configuring the Write Back Extreme extension

If you’ve prepared your environment with a webserver, PHP and a database, you are ready to install and configure the Write Back Extreme extension.

Point of attention

Avoid using passwords containing a period “.” This will break connections and result in unexpected behaviour.

5.1 Installation

First, unpack the zip file to /var/www/{write-back.company.com}/

Copy to Clipboard
Syntax Highlighter## upload writeback.zip to server # check if unzip is installed apt -y install unzip # unpack unzip -o writeback.zip -d /var/www/{write-back.company.com}/ # change owner to executing user (www-data is the standard used by NGINX) chown -R www-data:www-data /var/www/{write-back.company.com}/ # make sure the executable files has execute permissions # For example install.sh and update.sh and wbe-core.phar chmod +x install.sh

5.2 Install Write Back Extreme

This video is for version 1.2 and higher

5.3 Set file permissions

Copy to Clipboard
Syntax Highlighter# change owner to executing user (www-data is the standard used by NGINX) chown -R www-data:www-data /var/www/{write-back.company.com}/ # first is make sure all folders have proper permissions find . -type d -exec chmod 0755 {} \; # make sure all files have proper permissions find . -type f -exec chmod 0644 {} \;

5.4 Access backend

The system user (administrator = yes) you created in the “Configure application” step is used to access the backend via:

Copy to Clipboard
Syntax Highlighterhttps://{write-back.company.com}/backend/

5.5 Download the .trex file

The .trex file can be downloaded from the backend to share it with users. Click on the username (top right) and click on “Download .trex”.

Write Back Extreme – Installation Manual (Linux) write back extreme installation linux

What’s next?

Congratulations! You’ve successfully installed and configured the Write Back Extreme extension in your environment. Now that you’re ready to go, check out the Write Back Extreme Product Guide for complete documentation on how to use the extension.

Upgrade guide 1.x to 1.2 (Linux)

This upgrade is specially made when upgrading from 1.x to 1.2 or higher. If you are on 1.2 and are upgrading 1.3 or higher, you can follow the same steps. But you only to backup / overwrite the wbe-core.phar and run the ./update script.

Make sure you are on PHP version 7.4.15 or higher!

These steps are exactly the same for Windows. But instead of running shell script you run Windows script.

Steps:

Backup current directory
Extract new extension files in current directory
Restore LICENSE, .env, storage folder and migrations folder
Run Update script (if you are upgrading)
Make sure that permissions are correct for the newly added files (see below)
Done!

Update file permissions

Copy to Clipboard
Syntax Highlighter# change owner to executing user (www-data is the standard used by NGINX) chown -R www-data:www-data /var/www/{write-back.company.com}/ # first is make sure all folders have proper permissions find . -type d -exec chmod 0755 {} \; # make sure all files have proper permissions find . -type f -exec chmod 0644 {} \;

Important for Snowflake users: It’s required to have a driver configuration `NoExecuteInSQLPrepare=true`. Snowflake default executes [prepare statements](https://docs.snowflake.com/en/release-notes/2020-08.html#odbc-driver-2-21-6-changes-to-the-handling-of-ddl-statements-in-sqlprepare-and-sqlexecute) but it’s not default ODBC behaviour.

Finance

Healthcare

Retail

Finance

Healthcare

Retail

Linux WBE Installation

Skill level

Operating System

Scope

Points of attention

1. Preparation

2. Installing a webserver

NGINX

Apache2

NGINX

Install NGINX

Apache2

Install Apache2/PHP Apache2

3. Installing PHP 7.4.15 or higher

4. Installing a repository database

Points of attention

MySQL/MariaDB

MS SQL Server

PostgreSQL/Redshift

SQLite

Snowflake

OracleDB

MySQL/MariaDB

Install MySQL/MariaDB

Points of attention

Install MySQL

Configure MySQL

Install MariaDB

Configure MariaDB

MySQL and MariaDB – Create database users

MS SQL Server

Install MS SQL

Points of attention

Install dev tools

ODBC driver

PHP extension

PostgreSQL/Redshift

Install PostgreSQL

Points of attention

SQLite

Install SQLite

Points of attention

Snowflake

Install Snowflake

Points of attention

Configure ODBC for Snowflake

ODBC Setup

OracleDB

Install OracleDB

Points of attention

5. Installing and configuring the Write Back Extreme extension

Point of attention

5.1 Installation

5.2 Install Write Back Extreme

5.3 Set file permissions

5.4 Access backend

5.5 Download the .trex file

What’s next?

Upgrade guide 1.x to 1.2 (Linux)

Update file permissions