Accessing ODBC data sources from Snort

Contents

Introduction

Snort is an open-source network intrusion detection system. Snort alerts and logs may be written to a database using the database plugin. This article contains instructions on running Snort with the Easysoft ODBC-ODBC Bridge to log intrusions into remote ODBC data sources. Easysoft have tested Snort 1.8.1–1.9.0 with unixODBC 2.0.9–2.2.4 and ODBC-ODBC Bridge 1.0.0.32–1.0.0.42.

We found that there were a few problems with the ODBC support in Snort 1.8.[123] and 1.9, 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.

We have not yet tested Barnyard (the Snort log and alert spool processor) plugin for Snort.

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:

  1. Rename u_handle and u_environment.
  2. Address unusual mix of ODBC 2.0 and ODBC 3.0 calls. (For example, SQLAllocEnv and SQLFreeHandle.)
  3. The Snort code assumes the only way you can access Microsoft SQL Server is through the DB-Library, but you can also access 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.
  4. Added odbc_errors() function, which uses SQLError to retrieve ODBC error diagnosticss.
  5. Transaction support appears to have been added to Insert() using BEGIN, COMMIT, and ROLLBACK. However, the SQL-92 standard is BEGIN TRANSACTION not just BEGIN. In any case, ODBC provides transaction support with SQLTransact, so use that.
  6. The timestamp created with Database() is not ODBC conformant. However, code already existed for SQL Server that was correct for ODBC too.
  7. snort_escape_string() was attempting to escape a lot of characters it did not need to. It was also using the wrong escape character. Only ' needs to be escaped by doubling it up.
  8. CheckDBVersion() seems to have #ifndef ENABLE_MSSQL around code testing whether dbtype_id == DB_MSSQL. The schema table would not be escaped properly, therefore. The escaping was also required for ODBC.
  9. 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.
  10. The ODBC code uses SQLPrepare and SQLExecute unnecessarily when SQLExecDirect could be used instead, thus saving a call.
  11. The ODBC code in Select() to test if any data was returned is incorrect and only works by accident. It uses the SQLRowCount, which rarely returns anything other than -1 for a SELECT statement. We changed the code to use SQLNumResultCols.
  12. The ODBC code in Connect() was checking the return status with SQL_SUCCESS. This caused snort not to accept perfectly good connections. Many ODBC drivers return SQL_SUCCESS_WITH_INFO for SQLConnect calls. (For example, SQL Server reports language or database changed on connection). We changed the code to use the SQL_SUCCEEDED macro.
  13. The ODBC connection handle leaked on termination, which caused the freeing of the environment to fail. We added SQLFreeConnect call to Disconnect(). We also added code to free up the statement in the Disconnect().

To apply the patch follow the instructions at the end of the 1.8.1 notes below, except use the patch file Snort-1.9.0.PATCH.

The create_mssql file can be used to create the tables and indexes. 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 doesn't 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

Refer to 1.8.1 as the same comments apply.

A note on Snort 1.8.2

Refer to 1.8.1 as the same comments apply.

A note on Snort 1.8.1

We discovered a number of problems with Snort release 1.8.1 with respect to database support:

  1. spo_database.c has a hard-coded escape character ([) for SQL Server, but this is only available if ENABLE_MSSQL is defined. This macro appears to assume that you're building on Windows. The fix was to use SQLGetInfo to get the quote character the database requires.
  2. spo_database.c was leaking an ODBC statement handle every time some SQL was executed.
  3. The connection to the database was not closed down properly: no call to free the connection handle was made before freeing the environment. We also added an SQLEndTran call to be on the safe side.
  4. The code was not using the ODBC specification for timestamps.

You can apply a patch for these fixes (Snort-1.8.1.PATCH) to Snort 1.8.1, 1.8.2, and 1.8.3:

  1. Unpack the snort 1.8.[123] tar file.
  2. cd into the created directory.
  3. cat our patch file through patch. For example: 
    cat /path/Snort-1.8.1.PATCH | patch -p0

    The patch should apply cleanly. If it doesn't, you're probably attempting to apply it to the wrong version of Snort.

Pre-installation instructions

Before configuring Snort, install the Easysoft ODBC-ODBC Bridge. Make sure you choose to install the included unixODBC Driver Manager, if you don't already have unixODBC installed. Install the ODBC-ODBC Bridge client into unixODBC.

You need to know the path to the unixODBC files when configuring Snort. For example, if you install the ODBC-ODBC Bridge's unixODBC component, the default unixODBC path is /usr/local/easysoft/unixODBC.

Create an ODBC-ODBC Bridge client DSN (refer to DSN_definition.txt and example_odbc.ini in the ODBC-ODBC Bridge distribution). Test this DSN using unixODBC's isql utility and fully convince yourself it's working before continuing on to configuring and building Snort.

Configuring and installing 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 directory path where unixODBC is installed. (This is the --prefix argument on unixODBC's configure line or /usr/local/easysoft/unixODBC, if you're using the unixODBC included with the ODBC-ODBC Bridge.)

If you're using Snort 1.8.1–1.8.3, refer to the patch notes, earlier in this document.

Configure Snort --with-odbc=path, before building and installing Snort.

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

–Or–

# 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 your ODBC-ODBC Bridge DSN and user and password are the database user name and password. (For example, if your DSN points to a SQL Server DSN and your SQL Server DSN does not use trusted connections, these would be your SQL Server user name and password.)

When you run Snort, you must not start it with the -A or -s command line arguments, which prevent data from being written through the DB plugin.

Creating the Snort tables in your database

Read the README.database file in the Snort distribution.

Find the contrib/create_xxx.sql file in the Snort distribution that's closest to the syntax your database requires. We used the create_mssql.sql file and had an ODBC-ODBC Bridge DSN pointing at 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:

  1. Edit the .sql file you have chosen and make sure each SQL statement occupies only one line.
  2. Pass the edited file to isql like this: 
    isql -v dsn_name < edited_create_xxx.sql

    dsn_name should be the DSN that you created earlier.

If you get any errors, you may need to re-edit the .sql file and correct any syntax errors.

Running Snort

Run snort -c snort.conf and check your database for Snort data.