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!

Comments

  1. Hi,
    I managed to install to get it working, but for some reason I am unable to start scheduler. Any idea what might be problem?

    ReplyDelete
  2. This is awesome, especially the change in the installer.pm. Thank you so much!

    ReplyDelete
  3. Hey, Thanks for the detailed information. I will definitely try your tips and notes. SQL Server Load Soap API

    ReplyDelete

Post a Comment

Popular posts from this blog

that new old thing: a date2num implementation in vb.net

Getting Started with GitHub and Visual Studio 2017