Installing OTRS 4.0 on Ubuntu Linux 14.04 with a MSSQL Backend

A couple of quick notes... this is not my usual style, this is fairly cutting edge with OTRS 4.0 released yesterday. I've been tinkering around with the beta for a while, and I wanted to do the install from scratch AND use Microsoft SQL Server for the data store. If you have been trying to do this yourself, like me you discovered it's not officially supported. The OTRS group wants you to install on Windows (blech).

I'm here to tell you, this is possible, with some extra fiddling around. The biggest fiddle is getting an ODBC driver installed on Linux that will talk to Microsoft SQL Server, and is free.

I used Microsoft SQL Server 2008 as my backend server, but I imagine any SQL Server version will do. Without further adieu, here's my notes. This is basically a rough draft, if you find this interesting and need more info, leave a comment.

# Download source

cd /opt

wget http://ftp.otrs.org/pub/otrs/otrs-4.0.1.tar.gz

# Install Pre-requisites

apt-get install apache2

apt-get install libapache2-mod-perl2 libtimedate-perl libnet-dns-perl libnet-ldap-perl libio-socket-ssl-perl libpdf-api2-perl libsoap-lite-perl libgd-text-perl libtext-csv-xs-perl libjson-xs-perl libgd-graph-perl libapache-dbi-perl

apt-get install libdbd-sybase-perl

# Enable Perl module for apache

a2enmod perl

# Download official SQL Server driver for linux

# Credit - http://onefinepub.com/2013/03/ms-sql-odbc-ubuntu/

wget http://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/RedHat6/msodbcsql-11.0.2270.0.tar.gz

tar -zxvf msodbcsql-11.0.2270.0.tar.gz

# Install unixODBC

# Credit - http://onefinepub.com/2014/03/installing-unixodbc-2-3-2-higher-ubuntu-12-04-lts/

apt-get remove libodbc1 unixodbc unixodbc-dev

apt-get install build-essential

apt-get install openssl libkrb5-3 libc6 e2fsprogs

wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz

tar -zxvf unixODBC-2.3.2.tar.gz

cd unixODBC-2.3.2

./configure --enable-gui=no --enable-drivers=no --enable-stats=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --libdir=/usr/lib/x86_64-linux-gnu --prefix=/usr --sysconfdir=/etc

make install

cd ..

cd msodbcsql-11.0.2270.0

mv build_dm.sh build_dm.ms

mv install.sh install.ms

# Get build scripts

# Credit - http://onefinepub.com/2014/03/installing-unixodbc-2-3-2-higher-ubuntu-12-04-lts/

wget https://raw.github.com/Andrewpk/Microsoft--SQL-Server--ODBC-Driver-1.0-for-Linux-Fixed-Install-Scripts/master/build_dm.sh

wget https://raw.github.com/Andrewpk/Microsoft--SQL-Server--ODBC-Driver-1.0-for-Linux-Fixed-Install-Scripts/master/install.sh

chmod u+x install.sh; sudo bash install.sh install --accept-license --force

# Create needed symlinks for ODBC Driver to work

ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.10

ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.10

ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbcinst.so.1

ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbc.so.1

# Test SQL Driver directly

sqlcmd -S{servername} -U{username} -P{password}

# Create ODBC Driver Entry

vim /etc/odbcinst.ini

[SQL Server]

Description=Microsoft ODBC Driver 11 for SQL Server

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

Threading=1

UsageCount=1

# Create ODBC Test DSN

vim /etc/odbc.ini

[testdsn]

Driver=SQL Server

Description=My Test SQL Server

Trace=Yes

Server={servername}

Port=1433

# Test ODBC Driver connectivity to SQL Server

isql testdsn {username} {pass}

# Create OTRS user (local)

useradd -r -d /opt/otrs/ -c 'OTRS user' otrs

usermod -G nogroup otrs

# Unpack OTRS Source

cd /opt

tar -zxvf otrs-4.0.1.tar.gz

mv otrs-4.0.1 otrs

cd /opt/otrs/Kernel/

cp Config.pm.dist Config.pm

cd Config

cp GenericAgent.pm.dist GenericAgent.pm

# Edit Installer script, this is where the magic happens

vim /opt/otrs/Kernel/Modules/Installer.pm

# Comment out the code section below that disables mssql install on Linux !!!

>> # OTRS can only run on SQL Server if OTRS is on Windows as well

>> if ( $^O ne 'MSWin32' ) {

>> delete $Databases{mssql};

>> }

# Fix the ODBC Driver String so it's compatible with Linux !!!

>> :%s/DBI:ODBC:driver/DBI:ODBC:DRIVER/g

>> :wq

# Set permissions per the OTRS instructions

cd /opt/otrs/bin

./otrs.SetPermissions.pl /opt/otrs --otrs-user=otrs --web-group=www-data

./otrs.CheckModules.pl

service apache2 restart

# Launch the OTRS installer web page

>> http://(servername}/otrs/installer.pl

# Follow the instructions

# select mssql, enter your server name, 'sa' or equivalent account, etc

# Click Test-connection

# It works! amirite?  If this works for you or you find any errors, please comment and let me know what you think!