Accessing ODBC Data Sources from Snort
This file contains instructions on running Snort with the Easysoft ODBC-ODBC Bridge to log intrusions into a remote ODBC database. Easysoft have tested Snort 1.8.1 - 1.9.0 with unixODBC 2.0.9 - 2.2.4 and OOB 220.127.116.11 - 18.104.22.168.
We found that there were a few problems in Snort 1.8. and 1.9 versions in the ODBC support so produced patches which have been submitted to the Snort DB team. Hopefully these will be included in a future release but until then our patches are included here. November 2002 - snort patches for 1.8.n appear to have been ignored and so new 1.9 patches produced.
We have not yet tested Barnyard (the Snort log and alert spool processor) plugin for Snort.
- A note on Snort 1.9.0
- A note on Snort 1.8.3
- A note on Snort 1.8.2
- A note on Snort 1.8.1
- Pre-Installation Instructions
- Configuring/Installing Snort
- Create the Snort tables in your database
- Running Snort
- Useful Links
Snort is an open source network intrusion detection system. Snort alerts and logs may be written to a database using the database plugin. If Snort is built against the unixODBC driver manager and the OOB Client is installed Snort can then log to remote ODBC data sources.
A note on Snort 1.9.0
When Snort 1.9 was released we checked it for the inclusion of the patches we had made available to the snort team but unfortunately found no evidence of them. In addition, new features had been added and we found a new set of problems for ODBC. We have provided a patch to 1.9 and this has also be mailed to the Snort team. The patch makes the following changes:
- rename u_handle, u_environment (that is what it is)
- unusual mix of ODBC 2.0 and ODBC 3.0 calls (e.g. SQLAllocEnv and SQLFreeHandle). Since very free ODBC calls are made standardised on ODBC 2.0.
- The code assumes the only way you can access MS SQL Server is through the DB-Library but you can access MS SQL Server through ODBC. The hard-wired '' characters around the "schema" table should really be using the ODBC driver's SQL_IDENTIFIER_QUOTE_CHAR. This may apply to other tables too in other databases with ODBC drivers but only the "schema" one changed in this fix.
- Code not using ODBC diagnostics which makes diagnosing an error a pain. Added odbc_errors() function which uses SQLError() to retrieve ODBC error diags.
- Transaction support appears to have been added to Insert() using "BEGIN", "COMMIT" and "ROLLBACK" but the SQL92 is "BEGIN TRANSACTION" not just "BEGIN". Any case, ODBC has transaction support using SQLTransact() so use that.
- The timestamp created in Database() is not ODBC standard. However, code already existed for MS SQL Server that was correct for ODBC too.
- snort_escape_string() was attempting to escape a lot of characters  it did not need to and  used the wrong escape (i.e. it is not '\'). Only ' needs to be escaped by doubling it up.
- CheckDBVersion() seemed to have #ifndef ENABLE_MSSQL around code testing if the dbtype_id == DB_MSSQL and hence the "schema" table would not be escaped properly. The escaping also was required for ODBC.
- The ODBC code was leaking a statement handle every time Select() or Insert() was called. Code now allocates one statement handle and closes it after each SQLExecDirect.
- The ODBC code was using SQLPrepare/SQLExecute unnecessarily when SQLExecDirect() could be used - saves a call.
- ODBC code in Select() to test if any data was returned was incorrect and only working by accident. It used SQLRowCount which rarely returns anything other than -1 with most ODBC drivers for a select statement. Code changed to use SQLNumResultCols() and check resultant columns is only 1.
- The ODBC code in Connect() to connect to the database was checking the return status with SQL_SUCCESS causing snort to not accept perfectly good connections. Many ODBC drivers return SQL_SUCCESS_WITH_INFO for SQLConnect calls (i.e. MS SQL Server reports language or database changed on connection). Code changed to use the SQL_SUCCEEDED macro.
- ODBC connection handle leaked on termination and causing the freeing of the environment to fail - added SQLFreeConnect call to Disconnect(). Also added code to free up the statement in Disconnect().
To apply the patch follow the instructions at the end of the 1.8.1 notes below except using file patch file Snort-1.9.0.PATCH.
The create_mssql file can be used to create the tables and indexes - see the instructions under "Create the Snort tables in your database" below. However, we had problems with one table. The "sensor" table contains a column called "last_cid" which is created as NOT NULL but then spo_database.c does not insert a value into it. This causes errors with most databases. The solution is to remove the NOT NULL from the "last_cid" column definition.
A note on Snort 1.8.3
Please see 1.8.1 below as all the same comments apply.
A note on Snort 1.8.2
Please see 1.8.1 below as all the same comments apply.
A note on Snort 1.8.1
We discovered a number of problems with Snort release 1.8.1 in respect of the database support. The basic problems we found were:
- spo_database.c had a hard-coded escape chr which was '[' for MS SQL Server but only available if ENABLE_MSSQL defined. Defining this macro appeared to assume you were building on Windows. The fix was to use SQLGetInfo to get the quote chr the database requires (this is db driver independent).
- spo_database.c was leaking an ODBC statement handle every time some SQL was executed.
- The connection to the database was not closed down properly - no call to free the connection handle before freeing the environment. We also added an SQLEndTran call to be on the safe side.
- The code was not using the ODBC spec for timestamps.
The patch for these fixes may be applied to Snort 1.8.1, 1.8.2 and 1.8.3.
You should have the file Snort-1.8.1.PATCH.
To apply the patch:
- unpack the snort 1.8. tar file.
- cd into the created directory.
- cat our patch file through patch
cat /path/Snort-1.8.1.PATCH | patch -p0
The patch should apply cleanly. If it does not then you are probably attempting to apply it to the wrong version of Snort.
Before configuring Snort, install the Easysoft ODBC-ODBC Bridge making sure you select to install the included unixODBC driver manager if you do not already have unixODBC installed. Also make sure you select to install the OOB ODBC driver into unixODBC.
You need to know the path to the unixODBC files when configuring Snort. e.g. if you install OOB and its included unixODBC then the path to unixODBC will be
Create a DSN for unixODBC using the OOB driver - see the other files in this directory for help on this (e.g.
example_odbc.ini). Test this DSN using unixODBC's isql utility and fully convince yourself it is working before continuing on to configuring and building Snort.
To build Snort with ODBC support provided by unixODBC you need to use the
--with-odbc=path option to configure.
path should be the path to the directory where unixODBC was installed (it was the
--prefix argument to unixODBC's configure or
/usr/local/easysoft/unixODBC if you are using the unixODBC included with OOB).
If you are using Snort 1.8.1 - 1.8.3 see the patch note above.
--with-odbc=path, build and install.
You now need to edit the
snort.conf file to define the output database. You should find lines in the supplied
snort.conf file commented out like this:
# output database: log, unixodbc, user=snort dbname=snort
# output database: log, odbc, user=snort dbname=snort
You should uncomment this line and change the line to something like:
output database: alert, odbc, user=dbusername password=dbpassword dbname=snort
where dbname is the name of the DSN you have created in unixODBC and user/password are the database username and password (e.g. if your DSN pointed to MS SQL Server and the Server DSN was not using trusted connections then these would be your MS SQL Server username/password).
This is all the configuration you need for Snort in relation to DB access. However, please note that when you come to run Snort you must NOT start it with -A or -s command line arguments or data will NOT be written through the DB plugin.
Create the Snort tables in your database
Read README.database in the Snort distribution.
contrib/create_xxx.sql file in the Snort distribution which is closest to the syntax your database requires. We used the
create_mssql.sql file and had an OOB DSN pointing at MS SQL Server. The
.sql file you choose is probably formatted to be easily read but the unixODBC isql utility requires each SQL statement to be on one whole line. As a result, the best way to create the tables in your database is to:
- edit the .
sqlfile you have chosen and make sure each SQL statement occupies only one line.
- pass the file edited in  to isql like this:
isql -v dsn_name < edited_create_xxx.sql
dsn_nameshould be the DSN you created above.
If you get any errors here you may need to re-edit the
.sql file and correct syntax errors etc.
If you have completed all of the above all you should need to do is run
"snort -c snort.conf" and watch your database fill up with Snort data.
http://www.snort.org - The Open Source Network Intrusion Detection System
http://www.incident.org/snortdb - This site contains the latest information about database support for the Snort intrusion detection system. It also contains a list of applications capable of reading Snort db.