[Snort-sigs] Log to MS SQL

David Hollis dhollis at ...769...
Wed Aug 21 07:19:13 EDT 2002


The snort db output plugin should be easy to extend to support FreeTDS. 
That should provide MSSQL capability without too much effort.

On Wed, 2002-08-21 at 06:38, Szilagyi Gergely wrote:
> 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
> 
> 
> 
> -------------------------------------------------------
> 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