MonitorWare Knowledge Base

Hi!

I want to send the log data to mssql database. However when i start the rsyslog i get the
following error.

libdbi error: libdbi could not be initialized - suspending

I set up the following config file:
$ModLoad omlibdbi
$ActionLibdbiDriver freetds
$ActionLibdbiHost server
$ActionLibdbiUserName user
$ActionLibdbiPassword pwd
$ActionLibdbiDBName syslog_db
*.* :omlibdbi:

/etc/freetds/freetds/freetds.conf:
[server]
Host = server
Port = 1433
Tds version = 7.0


I did the following:
apt-get install freetds-common
apt-get install tdsodbc unixodbc libct4
apt-get install libdbi0
apt-get install libdbi0-dev

download the source and
./configure –enable-libdbi

When i start i get the libdbi error. How can i solve this?
Should i configure odbc too?

Thanks in advance ...

• Custom written rsyslog.conf?
• Maintenance Contract?
• Installation support?

I suggest that you enable debug logging, that hopefully tells us a bit more about this issue. Details here:

http://www.rsyslog.com/doc-troubleshoot.html

It may be useful to post a few hundered lines before the error message and maybe a handful after it.

Rainer

Ok, I've been hammering away at the same issue all day long and here is what I've come up with.

I've installed freetds
I've installed libdbi, the libdbi-driver compiled using the --with-freetds option.
I've installed unixODBC

Here are the relevent config files that I'm using right now

rsyslog.conf

$ModLoad omlibdbi.so
$ActionLibdbiDriver freetds
$ActionLibdbiHost resvasql01
$ActionLibdbiUserName rsyslog
$ActionLibdbiPassword pAssword123
$ActionLibdbiDBName syslog
*.* mlibdbi:

odbc.ini

[syslog]
Description = MSSQL
Driver = MSSQL
Servername = resvasql01
Server =
Address =
Port = 1433
Database = syslog
TDS_Version = 8.0
Language = us_english
TextSize =
Domain =
PacketSize =
Trace = Yes
TraceFile = /tmp/odbc_tr

odbcinst.ini
[MSSQL]
Description = MS SQL 2005
Driver = /usr/local/lib/libtdsodbc.so
Driver64 =
Setup = /usr/local/lib/libtdsodbc.so.0
Setup64 =
UsageCount = 1
CPTimeout =
CPReuse =

freetds.conf
# A typical Microsoft server
[resvasql01]
host = resvasql01
port = 1433
tds version = 8.0
client charset = UTF-8

Ok, I think that's all of them. I've tested connectivity to the DB using both the tsql command and the isql command, basically testing each part of the setup. Each one is able to make a successful connection in the DB and I am given a prompt to being CLI DB work. When I rsyslog attempts to make the connection I see the following written to the systemlog.

db error (-3): -3: libdbi could not establish a connection

So I started to dig deeper. Fired up wireshark and started sniffing the connection. My rsyslog host is attempting connections to the MS SQL server, but when it sends it's credentials, it's not sending the password causing a failed login. I've checked the TDS7/8 connection packets when using the above mentioned command lines and you see the password getting captured, but when rsyslog attemps the connection a password is simply not sent. Any idea what might be causing this? I'm going to try and poke at this further and see if it's possible to store the connection information within a different part of the actual connection and see if that resolves the issue, but I thought this information might help others with config issues and possible solutions.

Hey all,
Just a little bit ago I was finally able to get rsyslog to write some information to a MS SQL database. I've had to trouble shoot this via packet dumps and see what kind of errors were coming across the wire between the systems. First thing, Rsyslog still does not seem to want to send a password to the MSSQL server. To get around that, for testing only of course, I set the password to be {blank}. This lets me get around the first issue I was running up against. The next issues I was seeing dealt with improper formatting of the text trying to be written into the MS SQL fields. First ones were TIME/DATE related as the default template wants to use MYSQL timestamp formatting. That will be another issue to try and work through but ultimately what I ended up doing was taking it down to bare bones and seeing if I could populate just one field. So I setup a template specifically to only log %msg% to the 'message' field. This I was able to successfully do. From here I'll probably try and work backwards to slowly include extra fields and work through any errors getting logged. It would be nice if the password issue could be resolved though. Again I'm not sure if I just have a syntax issue going on or if it's something deeper with rsyslog itself. Any insight into this is greatly appreciated. If I get this fully functional I will for sure be doing a write up on how the whole thing was put together.

A quick note some of the errors I'm seeing being sent by my SQL server in it's response packets to my rsyslog server are this.

Conversion failed when converting the Varchar value 'RESVABU1' to data type int RESVASQL01. These two names are a server trying to log a message to rsyslog and the sqlserver itself respectivly.

Is there any update on this?

I have a nice test setup that is logging Windows events transmitted by Snare, with a template that puts things in the proper fields in a MySQL database so that everything looks nice when viewed in phpLogCon with event log fields selected as the view.

The end goal is to use a MS SQL back end. I have FreeTDS and unixODBC installed and verified with tsql and isql, and I've rebuit rsyslog with libdbi support, but I can't connect to the MS SQL server with rsyslog. rsyslog reports in debug output:

3526.053409176:main queue:Reg/w0: Called LogError, msg: db error (-3): -3: libdbi could not establish a connection