snort + unixodbc + freetds + mssql

["Paulo Filipe Mira" <paulo.mira () soquimica pt>]

Bit of a problem here, sorry for the long post, but i want to
include as much relevant info as possible.

# ./snort -V

-*> Snort! <*-
Version 1.8.4-beta2 (Build 93)
By Martin Roesch (roesch () sourcefire com, www.snort.org)

#uname -a
Linux themis 2.2.18 #1 Tue Jan 9 11:22:58 EST 2001 i586 unknown

unixODBC 2.2.0, FreeTDS 0.53 on localhost and MS SQL Server 2000 on a remote
w2k sp2 box.

Using unixODBC's isql i can connect to MSSQL just fine:

[root@themis snort-stable]# isql snortDB username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT sid FROM sensor WHERE hostname = 'themis' AND interface = 'eth0'
AND detail = '1' AND encoding = '0' AND filter IS NULL
query = SELECT sid FROM sensor WHERE hostname = 'themis' AND interface =
'eth0' AND detail = '1' AND encoding = '0' AND filter IS NULL
+-----------+
|           |
+-----------+
| 5         |
+-----------+
1 rows affected
SQL>

However:

[root@themis snort-stable]# ./snort -c /etc/snort/themis/snort.conf
Log directory = /var/log/snort

Initializing Network Interface eth0

        --== Initializing Snort ==--
Decoding Ethernet on interface eth0
Initializing Preprocessors!
Initializing Plug-ins!
Initializating Output Plugins!
Parsing Rules file /etc/snort/themis/snort.conf
<snip>
database: compiled support for ( odbc )
database: configured to use odbc
database:          user = username
database: password is set
database: database name = snortDB
database:   sensor name = themis
query = SELECT sid FROM sensor WHERE hostname = 'themis' AND interface =
'eth0' AND detail = '1' AND encoding = '0' AND filter IS NULL
query = INSERT INTO sensor (hostname, interface, detail, encoding) VALUES
('themis','eth0','1','0')
query = SELECT sid FROM sensor WHERE hostname = 'themis' AND interface =
'eth0' AND detail = '1' AND encoding = '0' AND filter IS NULL
database: Problem obtaining SENSOR ID (sid) from odbc->snort->sensor

 When this plugin starts, a SELECT query is run to find the sensor id for
the
 currently running sensor. If the sensor id is not found, the plugin will
run
 an INSERT query to insert the proper data and generate a new sensor id.
Then a
 SELECT query is run to get the newly allocated sensor id. If that fails
then
 this error message is generated.

 Some possible causes for this error are:
 * the user does not have proper INSERT or SELECT privileges
 * the sensor table does not exist

 If you are _absolutly_ certain that you have the proper privileges set and
 that your database structure is built properly please let me know if you
 continue to get this error. You can contact me at (jed () pickel net).

Fatal Error, Quitting..

Relevant line from snort.conf:

output database: log, odbc, user=username password=password dbname=snortDB
sensor_name=themis

This should not be a privileges-related problem, as the user is DBO of
snort's database.
Besides, same username is used on both isql and snort.conf.

The following are traces of FreeTDS talking to the SQL Server.

Using snort:

002-02-26 10:28:45 inside tds_process_default_tokens() marker is e3
2002-02-26 10:28:45 inside tds_process_default_tokens() marker is ab
2002-02-26 10:28:45 Msg 5701, Level 0, State 1, Server SERVER, Line 1
Changed database context to 'snort'.
2002-02-26 10:28:45 inside tds_process_default_tokens() marker is fd
SQLGetFunctions: fFunction is 999
Sending packet @ 2002-02-26 10:28:45
0000  01 01 01 04 00 00 01 00 53 00 45 00 4c 00 45 00   |........S.E.L.E.|
0010  43 00 54 00 20 00 73 00 69 00 64 00 20 00 46 00   |C.T. .s.i.d. .F.|
0020  52 00 4f 00 4d 00 20 00 73 00 65 00 6e 00 73 00   |R.O.M. .s.e.n.s.|
0030  6f 00 72 00 20 00 57 00 48 00 45 00 52 00 45 00   |o.r. .W.H.E.R.E.|
0040  20 00 68 00 6f 00 73 00 74 00 6e 00 61 00 6d 00   | .h.o.s.t.n.a.m.|
0050  65 00 20 00 3d 00 20 00 27 00 74 00 68 00 65 00   |e. .=. .'.t.h.e.|
0060  6d 00 69 00 73 00 27 00 20 00 41 00 4e 00 44 00   |m.i.s.'. .A.N.D.|
0070  20 00 69 00 6e 00 74 00 65 00 72 00 66 00 61 00   | .i.n.t.e.r.f.a.|
0080  63 00 65 00 20 00 3d 00 20 00 27 00 65 00 74 00   |c.e. .=. .'.e.t.|
0090  68 00 30 00 27 00 20 00 41 00 4e 00 44 00 20 00   |h.0.'. .A.N.D. .|
00a0  64 00 65 00 74 00 61 00 69 00 6c 00 20 00 3d 00   |d.e.t.a.i.l. .=.|
00b0  20 00 27 00 31 00 27 00 20 00 41 00 4e 00 44 00   | .'.1.'. .A.N.D.|
00c0  20 00 65 00 6e 00 63 00 6f 00 64 00 69 00 6e 00   | .e.n.c.o.d.i.n.|
00d0  67 00 20 00 3d 00 20 00 27 00 30 00 27 00 20 00   |g. .=. .'.0.'. .|
00e0  41 00 4e 00 44 00 20 00 66 00 69 00 6c 00 74 00   |A.N.D. .f.i.l.t.|
00f0  65 00 72 00 20 00 49 00 53 00 20 00 4e 00 55 00   |e.r. .I.S. .N.U.|
0100  4c 00 4c 00                                       |L.L.|


Received packet @ 2002-02-26 10:28:45
0000  81 01 00 00 00 10 00 6c 11 0a 00 03 73 00 69 00   |.......l....s.i.|
0010  64 00 d1 05 01 05 00 00 00 fd 10 00 c1 00 01 00   |d...............|
0020  00 00                                             |..|


2002-02-26 10:28:45 processing result tokens.  marker is  81
2002-02-26 10:28:45 processing result tokens.  marker is  d1

Using isql:

2002-02-25 16:30:31 inside tds_process_default_tokens() marker is e3
2002-02-25 16:30:31 inside tds_process_default_tokens() marker is ab
2002-02-25 16:30:31 Msg 5701, Level 0, State 1, Server SERVER, Line 1
Changed database context to 'snort'.
2002-02-25 16:30:31 inside tds_process_default_tokens() marker is fd
SQLGetFunctions: fFunction is 999
Sending packet @ 2002-02-25 16:30:34
0000  01 01 01 04 00 00 01 00 53 00 45 00 4c 00 45 00   |........S.E.L.E.|
0010  43 00 54 00 20 00 73 00 69 00 64 00 20 00 46 00   |C.T. .s.i.d. .F.|
0020  52 00 4f 00 4d 00 20 00 73 00 65 00 6e 00 73 00   |R.O.M. .s.e.n.s.|
0030  6f 00 72 00 20 00 57 00 48 00 45 00 52 00 45 00   |o.r. .W.H.E.R.E.|
0040  20 00 68 00 6f 00 73 00 74 00 6e 00 61 00 6d 00   | .h.o.s.t.n.a.m.|
0050  65 00 20 00 3d 00 20 00 27 00 74 00 68 00 65 00   |e. .=. .'.t.h.e.|
0060  6d 00 69 00 73 00 27 00 20 00 41 00 4e 00 44 00   |m.i.s.'. .A.N.D.|
0070  20 00 69 00 6e 00 74 00 65 00 72 00 66 00 61 00   | .i.n.t.e.r.f.a.|
0080  63 00 65 00 20 00 3d 00 20 00 27 00 65 00 74 00   |c.e. .=. .'.e.t.|
0090  68 00 30 00 27 00 20 00 41 00 4e 00 44 00 20 00   |h.0.'. .A.N.D. .|
00a0  64 00 65 00 74 00 61 00 69 00 6c 00 20 00 3d 00   |d.e.t.a.i.l. .=.|
00b0  20 00 27 00 31 00 27 00 20 00 41 00 4e 00 44 00   | .'.1.'. .A.N.D.|
00c0  20 00 65 00 6e 00 63 00 6f 00 64 00 69 00 6e 00   | .e.n.c.o.d.i.n.|
00d0  67 00 20 00 3d 00 20 00 27 00 30 00 27 00 20 00   |g. .=. .'.0.'. .|
00e0  41 00 4e 00 44 00 20 00 66 00 69 00 6c 00 74 00   |A.N.D. .f.i.l.t.|
00f0  65 00 72 00 20 00 49 00 53 00 20 00 4e 00 55 00   |e.r. .I.S. .N.U.|
0100  4c 00 4c 00                                       |L.L.|


Received packet @ 2002-02-25 16:30:34
0000  81 01 00 00 00 10 00 6c 11 0a 00 03 73 00 69 00   |.......l....s.i.|
0010  64 00 d1 05 01 05 00 00 00 fd 10 00 c1 00 01 00   |d.Ñ......ý..Á...|
0020  00 00                                             |..|


2002-02-25 16:30:34 processing result tokens.  marker is  81
2002-02-25 16:30:34 processing result tokens.  marker is  d1
SQLColAttributes: fDescType is 6
SQLColAttributes: fDescType is 18
2002-02-25 16:30:34 processing row tokens.  marker is  d1
2002-02-25 16:30:34 clearing column 0 NULL bit
SQLColAttributes: fDescType is 18
SQLColAttributes: fDescType is 6
2002-02-25 16:30:34 processing row tokens.  marker is  fd

I have made the following changes to snort's spo_database.c,
as per szilagyi () direkt-kfki hu's sugestion:

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';
        }
    ...
    ...
    ...


So, i guess what i'm asking is, has anyone been able to make the
pig squeal using this setup, and if so, what am i doing wrong?

Paulo Filipe Mira
SA/DBA



_______________________________________________
Snort-users mailing list
Snort-users () 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