[Snort-sigs] Log to MS SQL

Szilagyi Gergely szilagyi at ...765...
Wed Aug 21 03:36:01 EDT 2002


these are various letters about this problem by others and me, previously
appeared on SNORT-LIST.
I hope this helps, but there is another method, the one I'm using now:
on a linux box I use tcpdump or snort to capture to a binary file, with
minimal filtering, and then a script ssh-copies the b2ziped file to a
win-machine, where the win32 port of snort easily pumps it to an  MSSQL
database. Of course this scenario assumes that I capture WAN traffic, and
the linux and win boxes communicate through LAN. If you have any questions,
don't hesitate to ask!

bye
    Szilagyi Gergely
    gergely at ...766...

----------------------------------------------------------------------------
------------------------
Since MSSQL support is in beta state as far as I know, you can achieve the
same thing with odbc. I use snort on a linux box logging into an MSSQL2000
server on Win2k. Because I couldn't find native odbc to MSSQL on linux, I
use openlink's software, which is free to use for 2 concurrent users in 4
concurrent connections. Most of the time it's not much, but for snort it's
more than enough even with many sensor boxes logging into a central MSSQL
database. The tricky part for me was the compiling of snort on linux to get
everything working, because MSSQL and MySQL have some differences eg: the
way MSSQL handles datetime format. So if you plan to try this out I can send
you the modifications I made. (not much because MSSQL support is already in
beta state so I needed only a few #define directives) And again: check out
http://www.openlinksw.com/.
 bye
    Gergely Szilagyi
    gergely at ...766...

> ----- Original Message -----
> From: "skadhi" <skadhi at ...767...>
> To: "loveshinobi" <loveshinobi at ...144...>
> Cc: <snort-users at lists.sourceforge.net>
> Sent: Tuesday, January 08, 2002 5:25 PM
> Subject: Re: [Snort-users] what changes are required to move from MySQL
> toMSSQL?
>
>
> > On Mon, 2002-01-07 at 09:02, loveshinobi wrote:
> > > hi all,
> > >
> > > i got a question which i hope someone can help me with it
> > >
> > > i have successfully setup a Snort sensor on a RH machine to connect to
a
> > > remote MySQL database. now, i need to use MSSQL instead of MySQl (boss
> > > preference...)
> > >
> > > question is what are the changes do i need to make? is it just a
simple
> > > matter of changing the DB plug-in to point to the MSSQL? or are there
> any
> > > other changes to be done?
> > that + loading the snort DB schema in the mssql db
> >
> >
> > --
> > /Saad Kadhi --  [skadhi at ...767...]
> > [pgp keyid: 35592A6D http://pgp.mit.edu]
> > # buy a geek-in-a-can, point nozzle at technical problem and spray
> > # if desesperate degauss your screen. it might solve your pb as well
> >
> >
> > _______________________________________________
> > Snort-users mailing list
> > Snort-users at lists.sourceforge.net
> > Go to this URL to change user options or unsubscribe:
> > https://lists.sourceforge.net/lists/listinfo/snort-users
> > Snort-users list archive:
> > http://www.geocrawler.com/redir-sf.php3?list=snort-users
>


Hi!
Here is what I tried: /and it worked:) /

1. Download (after some simple registraton process) from
http://www.openlinksw.com/ 3 packages:
    a:    MultiTier Requestbroker server for MSSQL on Win32 /actually it's
not the name but I'm sure you'll find it/
    b:    Linux IODBC RB
    c:    Linux IODBC.sdk
2. Setting up is fairly easy because you always download 2 files for a linux
install: *.taz + install.sh. If they're in the same directory just sh
install.sh and that's it. There will be some questions about your desired
JDK version because it's a bundled package with a full extras, but you
shouldn't care much, we're dealing with ODBC instead of JDBC. The win32
install is really simple, it has a nice InstallShield GUI, I suggest you'd
leave most options as default.
3. If you're done you'll have a mini-webserver configuration interface on
each machine on the 8000 port. There you'll set up the server options for
the server, and the client options for the client. (tricky huh...) No, it's
really self-explanatory, just be sure that you'll have the neccessary
environment variables set and exported on your linux box, as seen in
/iodbc/openlink.sh.
4. Change spo_database.c like this:
***********************************
/* Function: CheckDBVersion(DatabaseData * data)
 *
 * Purpose: To determine the version number of the underlying DB schema
 *
 * Arguments: database information
 *
 * Returns: version number of the schema
 */
int CheckDBVersion(DatabaseData * data)
{
  char *select0;
  int schema_version;

  select0 = (char *) malloc (MAX_QUERY_LENGTH+1);
  snprintf(select0, MAX_QUERY_LENGTH,
           /* "schema" is a keyword in SQL Server, so quote it with square
brackets */
           "SELECT vseq FROM [schema]");

  schema_version = Select(select0,data);
  free(select0);

  return schema_version;
}
************************************
and
************************************
/*
 * Function: Database(Packet *, char * msg, void *arg)
 *
 * Purpose: Insert data into the database
 *
 * Arguments: p   => pointer to the current packet data struct
 *            msg => pointer to the signature message
 *
 * Returns: void function
 *
 */
void Database(Packet *p, char *msg, void *arg, Event *event)
{
    DatabaseData *data = (DatabaseData *)arg;
    SQLQuery * query;
    SQLQuery * root;
    char * tmp, *tmp1, *tmp2, *tmp3;
    char * tmp_not_escaped;
    int i;
    char *select0, *select1, *insert0;
    unsigned int sig_id;
    extern OptTreeNode *otn_tmp;  /* rule node */
    ReferenceData *ds_ptr;
    PriorityData *class_ptr;
    int ref_system_id;
    unsigned int ref_id, class_id=0;

    query = NewQueryNode(NULL, 0);
    root = query;

    if(msg == NULL)
    {
        msg = "";
    }

    /*** Build the query for the Event Table ***/
    if(p != NULL)
    {
        tmp = GetTimestamp((time_t *)&p->pkth->ts.tv_sec, data->tz);
    }
    else
    {
        tmp = GetCurrentTimestamp();
    }
        /* SQL Server uses a date format which is slightly
         * different from the ISO-8601 standard generated
         * by GetTimestamp() and GetCurrentTimestamp().  We
         * need to convert from the ISO-8601 format of:
         *   "1998-01-25 23:59:59+14316557"
         * to the SQL Server format of:
         *   "1998-01-25 23:59:59.143"
         */
        if( tmp!=NULL && strlen(tmp)>=22 )
        {
            tmp[19] = '.';
            tmp[23] = '\0';
        }
    ...
    ...
    ...
    from here it goes unchanged.
**************************

for your convenience I attach my modified spo_database.c.
4. Compile Snort with your favourite options. I had a command line like
this:
./configure --with-mysql=no --with-odbc=/iodbc/odbcsdk/ --with-postgresql=no
 --with-oracle=no --without-snmp --with-openssl=no --with-libxml2-includes=n
o --with-libntp-libraries=no --with-libidmef-includes=no
    Here is one trick with /iodbc/odbcsdk: you should copy the include and
header and lib dirs in one dir from the 2 linux install packages, that
directory is /iodbc/odbcsdk for me.

5. put this in your snort.conf:
    output database: log, odbc, user=hawk password=*** dbname=pince1
    / I don't want to confuse you but you might be interested in what
^pince1^ means: it's ^base1^ in hugarian/
6. put something like this in your /iodbc/bin/odbc.ini :
*************************************
[ODBC Data Sources]
OpenLink = OpenLink Generic ODBC Driver
pince1   = OpenLink Generic ODBC Driver

[OpenLink]
Driver          = /iodbc/lib/oplodbc.so.1
Description     = Sample OpenLink DSN
Host            = localhost
ServerType      = Oracle 8.1.x
FetchBufferSize = 99
UserName        =
Password        =
Database        =
ServerOptions   =
ConnectOptions  =
Options         =
ReadOnly        = no
Trace           = 0
TraceFile       = /tmp/iodbc.trace

[Default]
Driver = /iodbc/lib/oplodbc.so.1

[pince1]
DeferLongFetch  =
Password        =
Description     = pince1
Options         =
Port            = 5000
Host            = xxx.xxx.xxx.xxx
UserName        = hawk
ServerType      = SQLServer 2000
Protocol        = TCP/IP
Driver          = /iodbc/lib/oplodbc.so.1
Database        = snortx
ReadOnly        =
NoLoginBox      =
FetchBufferSize = 99

[Communications]
BrokerTimeout  = 30
ReceiveTimeout = 120
RetryTimeout   = 5
ReceiveSize    = 16000
SendSize       = 4096
ShowErrors     = Y
DataEncryption = N

[ODBC]
DebugFile = /tmp/aaa.log
*****************************
The debug file can grow like mad, but it's very good at tuning your system.
basically you should see only one SQL_ERROR in this file for every snort
running, at the end of communication with the SQL server. I know it's an
error, but my system works fine with it.If you find out how to get rid of it
don't hesitate to tell me :)

I think that's it. If you still have problems tell me and I try to help.
Sorry for my bad english...
Bye
     Gergely Szilagyi
     gergely at ...766...


----- Original Message -----
From: "loveshinobi" <loveshinobi at ...144...>
To: "Szilagyi Gergely" <szilagyi at ...765...>
Sent: Thursday, January 10, 2002 2:48 AM
Subject: Re: [Snort-users] what changes are required to move from MySQL
toMSSQL?


> cool man :) COOL!!!! that's what i am looking for !
>
> i'll appreciate it if you can send me details of the modifications you
made
>
> a million thanks in advance :)
>
> cheers!
> heemeng
>
> ----- Original Message -----
> From: "Szilagyi Gergely" <szilagyi at ...765...>
> To: <Snort-users at lists.sourceforge.net>
> Sent: Wednesday, 09 January, 2002 6:18 PM
> Subject: Fw: [Snort-users] what changes are required to move from MySQL
> toMSSQL?
>
>
> > Since MSSQL support is in beta state as far as I know, you can achieve
the
> > same thing with odbc. I use snort on a linux box logging into an
MSSQL2000
> > server on Win2k. Because I couldn't find native odbc to MSSQL on linux,
I
> > use openlink's software, which is free to use for 2 concurrent users in
4
> > concurrent connections. Most of the time it's not much, but for snort
it's
> > more than enough even with many sensor boxes logging into a central
MSSQL
> > database. The tricky part for me was the compiling of snort on linux to
> get
> > everything working, because MSSQL and MySQL have some differences eg:
the
> > way MSSQL handles datetime format. So if you plan to try this out I can
> send
> > you the modifications I made. (not much because MSSQL support is already
> in
> > beta state so I needed only a few #define directives) And again: check
out
> > http://www.openlinksw.com/.
> >  bye
> >     Gergely Szilagyi
> >     gergely at ...766...


Hi!
Hope this wil help, but if you have any other questions, don't hesitate to
drop me a mail.
Cheers
Gergely Szilagyi
gergely at ...766...

----- Original Message -----
From: "Szilagyi Gergely" <szilagyi at ...765...>
To: <Snort-users at lists.sourceforge.net>
Sent: Thursday, January 10, 2002 3:26 PM
Subject: Re: [Snort-users] what changes are required to move from MySQL
toMSSQL?


> Hi!
> Here is what I tried: /and it worked:) /
>
> 1. Download (after some simple registraton process) from
> http://www.openlinksw.com/ 3 packages:
>     a:    MultiTier Requestbroker server for MSSQL on Win32 /actually it's
> not the name but I'm sure you'll find it/
>     b:    Linux IODBC RB
>     c:    Linux IODBC.sdk
> 2. Setting up is fairly easy because you always download 2 files for a
linux
> install: *.taz + install.sh. If they're in the same directory just sh
> install.sh and that's it. There will be some questions about your desired
> JDK version because it's a bundled package with a full extras, but you
> shouldn't care much, we're dealing with ODBC instead of JDBC. The win32
> install is really simple, it has a nice InstallShield GUI, I suggest you'd
> leave most options as default.
> 3. If you're done you'll have a mini-webserver configuration interface on
> each machine on the 8000 port. There you'll set up the server options for
> the server, and the client options for the client. (tricky huh...) No,
it's
> really self-explanatory, just be sure that you'll have the neccessary
> environment variables set and exported on your linux box, as seen in
> /iodbc/openlink.sh.
> 4. Change spo_database.c like this:
> ***********************************
> /* Function: CheckDBVersion(DatabaseData * data)
>  *
>  * Purpose: To determine the version number of the underlying DB schema
>  *
>  * Arguments: database information
>  *
>  * Returns: version number of the schema
>  */
> int CheckDBVersion(DatabaseData * data)
> {
>   char *select0;
>   int schema_version;
>
>   select0 = (char *) malloc (MAX_QUERY_LENGTH+1);
>   snprintf(select0, MAX_QUERY_LENGTH,
>            /* "schema" is a keyword in SQL Server, so quote it with square
> brackets */
>            "SELECT vseq FROM [schema]");
>
>   schema_version = Select(select0,data);
>   free(select0);
>
>   return schema_version;
> }
> ************************************
> and
> ************************************
> /*
>  * Function: Database(Packet *, char * msg, void *arg)
>  *
>  * Purpose: Insert data into the database
>  *
>  * Arguments: p   => pointer to the current packet data struct
>  *            msg => pointer to the signature message
>  *
>  * Returns: void function
>  *
>  */
> void Database(Packet *p, char *msg, void *arg, Event *event)
> {
>     DatabaseData *data = (DatabaseData *)arg;
>     SQLQuery * query;
>     SQLQuery * root;
>     char * tmp, *tmp1, *tmp2, *tmp3;
>     char * tmp_not_escaped;
>     int i;
>     char *select0, *select1, *insert0;
>     unsigned int sig_id;
>     extern OptTreeNode *otn_tmp;  /* rule node */
>     ReferenceData *ds_ptr;
>     PriorityData *class_ptr;
>     int ref_system_id;
>     unsigned int ref_id, class_id=0;
>
>     query = NewQueryNode(NULL, 0);
>     root = query;
>
>     if(msg == NULL)
>     {
>         msg = "";
>     }
>
>     /*** Build the query for the Event Table ***/
>     if(p != NULL)
>     {
>         tmp = GetTimestamp((time_t *)&p->pkth->ts.tv_sec, data->tz);
>     }
>     else
>     {
>         tmp = GetCurrentTimestamp();
>     }
>         /* SQL Server uses a date format which is slightly
>          * different from the ISO-8601 standard generated
>          * by GetTimestamp() and GetCurrentTimestamp().  We
>          * need to convert from the ISO-8601 format of:
>          *   "1998-01-25 23:59:59+14316557"
>          * to the SQL Server format of:
>          *   "1998-01-25 23:59:59.143"
>          */
>         if( tmp!=NULL && strlen(tmp)>=22 )
>         {
>             tmp[19] = '.';
>             tmp[23] = '\0';
>         }
>     ...
>     ...
>     ...
>     from here it goes unchanged.
> **************************
>
> for your convenience I attach my modified spo_database.c.
> 4. Compile Snort with your favourite options. I had a command line like
> this:
>
./configure --with-mysql=no --with-odbc=/iodbc/odbcsdk/ --with-postgresql=no
>  --with-oracle=no --without-snmp --with-openssl=no --with-libxml2-includes
=n
> o --with-libntp-libraries=no --with-libidmef-includes=no
>     Here is one trick with /iodbc/odbcsdk: you should copy the include and
> header and lib dirs in one dir from the 2 linux install packages, that
> directory is /iodbc/odbcsdk for me.
>
> 5. put this in your snort.conf:
>     output database: log, odbc, user=hawk password=*** dbname=pince1
>     / I don't want to confuse you but you might be interested in what
> ^pince1^ means: it's ^base1^ in hugarian/
> 6. put something like this in your /iodbc/bin/odbc.ini :
> *************************************
> [ODBC Data Sources]
> OpenLink = OpenLink Generic ODBC Driver
> pince1   = OpenLink Generic ODBC Driver
>
> [OpenLink]
> Driver          = /iodbc/lib/oplodbc.so.1
> Description     = Sample OpenLink DSN
> Host            = localhost
> ServerType      = Oracle 8.1.x
> FetchBufferSize = 99
> UserName        =
> Password        =
> Database        =
> ServerOptions   =
> ConnectOptions  =
> Options         =
> ReadOnly        = no
> Trace           = 0
> TraceFile       = /tmp/iodbc.trace
>
> [Default]
> Driver = /iodbc/lib/oplodbc.so.1
>
> [pince1]
> DeferLongFetch  =
> Password        =
> Description     = pince1
> Options         =
> Port            = 5000
> Host            = xxx.xxx.xxx.xxx
> UserName        = hawk
> ServerType      = SQLServer 2000
> Protocol        = TCP/IP
> Driver          = /iodbc/lib/oplodbc.so.1
> Database        = snortx
> ReadOnly        =
> NoLoginBox      =
> FetchBufferSize = 99
>
> [Communications]
> BrokerTimeout  = 30
> ReceiveTimeout = 120
> RetryTimeout   = 5
> ReceiveSize    = 16000
> SendSize       = 4096
> ShowErrors     = Y
> DataEncryption = N
>
> [ODBC]
> DebugFile = /tmp/aaa.log
> *****************************
> The debug file can grow like mad, but it's very good at tuning your
system.
> basically you should see only one SQL_ERROR in this file for every snort
> running, at the end of communication with the SQL server. I know it's an
> error, but my system works fine with it.If you find out how to get rid of
it
> don't hesitate to tell me :)
>
> I think that's it. If you still have problems tell me and I try to help.
> Sorry for my bad english...
> Bye
>      Gergely Szilagyi
>      gergely at ...766...
>
>
> ----- Original Message -----
> From: "loveshinobi" <loveshinobi at ...144...>
> To: "Szilagyi Gergely" <szilagyi at ...765...>
> Sent: Thursday, January 10, 2002 2:48 AM
> Subject: Re: [Snort-users] what changes are required to move from MySQL
> toMSSQL?
>
>
> > cool man :) COOL!!!! that's what i am looking for !
> >
> > i'll appreciate it if you can send me details of the modifications you
> made
> >
> > a million thanks in advance :)
> >
> > cheers!
> > heemeng
> >
> > ----- Original Message -----
> > From: "Szilagyi Gergely" <szilagyi at ...765...>
> > To: <Snort-users at lists.sourceforge.net>
> > Sent: Wednesday, 09 January, 2002 6:18 PM
> > Subject: Fw: [Snort-users] what changes are required to move from MySQL
> > toMSSQL?
> >
> >
> > > Since MSSQL support is in beta state as far as I know, you can achieve
> the
> > > same thing with odbc. I use snort on a linux box logging into an
> MSSQL2000
> > > server on Win2k. Because I couldn't find native odbc to MSSQL on
linux,
> I
> > > use openlink's software, which is free to use for 2 concurrent users
in
> 4
> > > concurrent connections. Most of the time it's not much, but for snort
> it's
> > > more than enough even with many sensor boxes logging into a central
> MSSQL
> > > database. The tricky part for me was the compiling of snort on linux
to
> > get
> > > everything working, because MSSQL and MySQL have some differences eg:
> the
> > > way MSSQL handles datetime format. So if you plan to try this out I
can
> > send
> > > you the modifications I made. (not much because MSSQL support is
already
> > in
> > > beta state so I needed only a few #define directives) And again: check
> out
> > > http://www.openlinksw.com/.
> > >  bye
> > >     Gergely Szilagyi
> > >     gergely at ...766...
>
>
>
>


----- Original Message -----
From: "Robert Reid" <rreid at ...414...>
To: <snort-sigs at lists.sourceforge.net>
Sent: Tuesday, August 20, 2002 4:11
Subject: [Snort-sigs] Log to MS SQL


Cant seem to find any good information on this.

Is it possible for Snort to log to a SQL 7.0 or SQL 2000 DB?

Thanks





-------------------------------------------------------
This sf.net email is sponsored by: OSDN - Tired of that same old
cell phone?  Get a new here for FREE!
https://www.inphonic.com/r.asp?r=sourceforge1&refcode1=vs3390
_______________________________________________
Snort-sigs mailing list
Snort-sigs at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/snort-sigs





More information about the Snort-sigs mailing list