Connecting from Linux to Microsoft SQL Server for a Customer backend

Jens Bothe15. Jun 2012 | Consulting

Disclaimer:

The practical examples presented in our technical blog (blog.otrs.com) and now in the expert category in our FAQ blog section serve as a source of ideas and documentation to show what is theoretically possible with OTRS in concrete scenarios or sometimes even for more exotic configurations. All configurations presented here were developed under laboratory conditions as a proof of concept. 

We can only guarantee testing and implementation of these concepts to be error-free and productive if implemented in a workshop with one of our OTRS consultants. Without this, the responsibility lies with the customer himself. Please note that configurations from older OTRS versions may not work in the newer ones.

Recently I migrated one of our customers to a new platform. Previously they used SUSE Linux; but the company was acquired by a different company and now they wanted to standardize their platforms on Red Hat Enterprise Linux, or RHEL, for short.

They use a view to get their OTRS Customer Companies from their remote Microsoft Dynamics system and use it in OTRS. Previously this was done using the good old Sybase DBD driver and FreeTDS but this solution is a little tricky.

Microsoft released their Microsoft® SQL Server® ODBC Driver 1.0 for Linux with a lot of press and excitement. Currenty it is only available for RHEL5 or RHEL6, and only for 64-bit (or CentOS, of course, if you’re a cheapskate, or want to deploy on Azure Cloud). In this case the customer used RHEL6 so it was a perfect fit.

I installed the driver according to the instructions (requires a C compiler, you might need to do ‘yum install gcc‘ before you actually install). Installation is quite simple, there are convenient shell scripts that take care of all the compilation and stuff.

After installation you can test connection to your database using the built in sqlcmd tool. This is easy because you can swiftly fire off a few commands to see if you are “well connected” and if the firewall guy did his job. In this case there is a view in place on the CRM database that holds exactly the fields we want to use:

[otrs]$ sqlcmd -Ssqlserver.local -Uotrs -Psecretpass
1> use MY_CRMDB;
2> GO
Changed database context to 'MY_CRMDB'.
1> select count(*) from view_for_otrs;
2> GO

-----------
      45833

(1 rows affected)

1> quit
[otrs]$

so that is perfect! Now I had to define the connection to the Customer Company database. The tricky bit here is always the DSN connection, the connection string to the database. This is partly because it just seems like voodo to me, partly because OTRS tries to determine the database type from the DSN; so it knows what OTRS driver to use. For this it uses the connection string. In case of SQL Server, the words ‘sybase’ or ‘mssql’ should be there.

First, I configure an entry in /etc/odbc.ini:

[mssqldynamics]
Server   = tcp:sqlserver.local,1433
Driver   = SQL Server Native Client 11.0
Database = MY_CRMDB

and then I put the configuration in place in Kernel/Config.pm:

    # ---------------------------------------------------- #
    # CustomerCompany configuration
    # ---------------------------------------------------- #
    $Self->{CustomerCompany} = {
        Params => {
            # if you want to use an external database, add the
            # required settings
            DSN      => 'DBI:ODBC:mssqldynamics',
            User     => 'otrs',
            Password => 'secretpass',
            Table    => 'view_for_otrs',
        },

        # customer uniq id
        CustomerCompanyKey             => 'customer_id',
        CustomerCompanyValid           => 'valid_id',
        CustomerCompanyListFields      => [ 'name' ],
        CustomerCompanySearchFields    => [ 'name', 'customer_id' ],
        CustomerCompanySearchPrefix    => '',
        CustomerCompanySearchSuffix    => '*',
        CustomerCompanySearchListLimit => 250,

        Map => [
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'CustomerID',                  'CustomerID',    'customer_id',  0, 1, 'var', '', 1 ],
            [ 'CustomerCompanyName',         'Company',       'name',         1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyURL',          'URL',           'url',          1, 0, 'var', '$Data{"CustomerCompanyURL"}', 1 ],
        ],
    };

Please note that I used a connection string which starts with ‘mssql’ – the only reason for this is that OTRS now can recognize the connection as one to Microsoft SQL Server.

The database connection is using ODBC so it is not the fastest around, but it is good enough for a customer database or customer company database. I would not recommend to use this setup with the complete database of OTRS on SQL Server. Still, its nice to be able to connect to your SQL Server like this from OTRS.

The nicest way to get these data from CRM would obviously be to use a connector to their API, but that would require development, whereas this was configurable on OTRS out-of-the-box.

#2
Jens at 17.06.2014, 21:38

MS SQL as application database is not supported on Linux as there is no reliable database driver

#1
John at 29.03.2014, 04:36

Is it possible to use MS SQL as the database server for OTRS running on CentOS 6 with the MS ODBC driver? From everything I've read, OTRS has better performance on Linux, so we'd like to go that route if possible. We already have a high-available MS SQL server farm with replication that we want to leverage for the backend. The web installer for the latest version doesn't give the option for SQL Server. Do you have any insight on this? Thanks!

Your email address will not be published.