Czanik@BalaBit

During the past few months many people asked, how to log from syslog-ng to MS SQL on RHEL or CentOS. So, even if I don’t use MS SQL regularly, I put together a test environment, so I can answer this question in a short, how-to like blog.

This blog is based on an earlier one, where I wrote about MySQL and PostgreSQL. I left that information included, as it could be used as a reference for anybody interested logging into those databases. Obviously, related package installs and configure parameters can be skipped if one is interested only in the MS SQL destination.

syslog-ng uses the libdbi database abstraction layer to access databases. While it is included in RHEL 6, it does not support MS SQL (freetds) so one needs to compile it and install it after dependencies (like development versions of native database clients) are installed.

My test was concluded on CentOS 6.3 and with MS SQL 2008R2 on Windows 7, but should work also on RHEL 5 and 6 and derivatives and other MS SQL versions. There are a couple of limitations, most likely coming from the used freetds or libdbi libraries:

• only SQL authentication works (no Windows users)
• only the default database of the user can be accessed (changing the database name has no effect)

For the step by step instructions I used the latest syslog-ng release, version 3.4.1, but earlier releases should also work.

First install some packages necessary to build syslog-ng.

yum install gcc

yum install glib2-devel

yum install openssl-devel

Add /usr/local/lib to ld.so.conf:

echo /usr/local/lib > /etc/ld.so.conf.d/local.conf

Create a working directory, where you will download and compile syslog-ng and related software:

mkdir ~/workdir

Download and install eventlog:

cd ~/workdir

wget http://www.balabit.com/downloads/files/eventlog/0.2/eventlog_0.2.12.tar.gz

tar xzvpf eventlog_0.2.12.tar.gz

cd eventlog-0.2.12/

./configure

make

make install

Go back to the working directory:

cd ~/workdir

Download libdbi from sourceforge: http://sourceforge.net/projects/libdbi/files/libdbi/libdbi-0.8.4/

Compile and install it:

tar xzvpf libdbi-0.8.4.tar.gz

cd libdbi-0.8.4

./configure –disable-docs

make

make install

Go back to the working directory:

cd ~/workdir

Before going on with compiling libdbi-drivers, wich provides the actual drivers for libdbi one needs to install the native drivers. If you want also PostgreSQL and / or MySQL support, please also install the related development packages (names might be slightly different depending on RHEL version):

yum install postgresql-devel

yum install mysql-devel

Connecting to MS SQL is achieved through the freetds driver, which is not part of RHEL, so it needs to be compiled:

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-0.91.49.tar.gz

tar xzvpf freetds-0.91.49.tar.gz

cd freetds-0.91.49

./configure –with-tdsver=7.1

make

make install

The default for –with-tdsver is 5.0, which did not work with my installed SQL Server 2008R2. In the SQL Server logs I received messages about invalid packages and requests to check my client. You might need to experiment with this parameter, see ./configure –help for possible options.

Once all native clients are installed, go back to the working directory and download libdbi-drivers from sourceforge: http://sourceforge.net/projects/libdbi-drivers/files/libdbi-drivers/libdbi-drivers-0.8.3-1/

Compile and install it:

tar xzvpf libdbi-drivers-0.8.3-1.tar.gz

cd libdbi-drivers-0.8.3-1

Configuring is a bit more tricky, as it needs switches and additional development packages. The following example is for PostgreSQL and MySQL, use ./configure –help to see options for other databases. Beware, that sqlite only works with a CVS snapshot of libdbi (so it’s not shown here). If you don’t need support for one of the database drivers, simply remove the related options from the below example.

To configure, compile and install it use the following commands:

./configure –with-dbi-incdir=/usr/local/include/dbi –with-dbi-libdir=/usr/local/lib –with-mysql –with-mysql-dir=/usr –with-mysql-libdir=/usr/lib64/mysql –with-mysql-incdir=/usr/include/mysql/ –with-pgsql –with-pgsql-dir=/usr –disable-docs –with-freetds –with-freetds-dir=/usr/local/

make

make install

Using a self compiled database server will most likely need slightly different configure parameters, with directory parameters pointing to /opt or /usr/local instead of /usr.

Finally download, configure and install syslog-ng:

cd ~/workdir

https://www.balabit.com/downloads/files/syslog-ng/open-source-edition/3.4.1/source/syslog-ng_3.4.1.tar.gz

tar xzvpf syslog-ng_3.4.1.tar.gz

cd syslog-ng-3.4.1/

./configure PKG_CONFIG_PATH=/usr/local/lib/pkgconfig/

make

make install

ldconfig

The output of syslog-ng -V should now list “afsql” among the available modules.

If compiling all of these software manually is beyond your time an energy you should consider syslog-ng Premium Edition, which includes support for all of the above mentioned databases and even more with easy to deploy binaries for over 50 different platforms.

Suggested reading:

• Configuring MS SQL server to receive logs from syslog-ng
• Configuring syslog-ng to be used with an MS SQL destination