Re: what changes are required to move from MySQL toMSSQL?

["Szilagyi Gergely" <szilagyi () direkt-kfki hu>]

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 () szilagyi org


----- Original Message -----
From: "loveshinobi" <loveshinobi () yahoo com>
To: "Szilagyi Gergely" <szilagyi () direkt-kfki hu>
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 () direkt-kfki hu>
> To: <Snort-users () 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 () szilagyi org

Attachment: spo_database.c
Description: