Connecting from Linux to Microsoft SQL Server for a Customer backend

Jens Bothe15. Jun 2012 | Consulting

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