Barnyard2 can't connect to remote mysql

[rickyzhang]

I used pfsense 2.4.4-RELEASE-p3 with snort package. I tried to send alerts to MySQL server in my Dell T20 home server.

But Barnyard2 log show the following repeatedly.

Aug  3 22:58:47 pfsense.localdomain barnyard2[38593]: [Select()]: Failed to execute  query [SELECT vseq FROM `schema`] , will retry

I can used the credential to login from pfsense ssh shell. But it show no tables in the data set:

mysql> show tables;
Empty set (0.00 sec)

It seemed that it can't see the schema thus it can't proceed to create tables. Should the user be root in mysql?

Here is my MySQL server version:

Server version: 5.5.5-10.3.9-MariaDB-log MariaDB Server

[rickyzhang]

I initialized mysql database manually from pfsense router.

I downloaded create_mysql script from barnyard2 and create database and tables:

mysql --user=root --password=mypassword -P 3306 --host=192.168.2.30 snort_db < create_mysql

I grant permission to db user snort:

grant INSERT,SELECT on snort_db.* to snort;
grant INSERT,SELECT,UPDATE on snort_db.sensor to snort;

However, barnyard2 still failed:

103 Aug  4 06:51:33 pfsense.localdomain barnyard2[66013]: ===============================================================================
104 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: [CacheSynchronize()]:, SystemCacheSyncronize() call failed.
105 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: [SystemPullDataStore()]: Failed exeuting query [SELECT ref_system_id, ref_system_name FROM reference_system;] , will retry
106 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: FATAL ERROR: database [DatabaseInitFinalize()]: CacheSynchronize() call failed ...
107 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: Barnyard2 exiting
108 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: database: Closing connection to database "snort_db"
109 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: Record Totals:
110 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]:    Unknown:           0 (0.000%)
111 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]:    Suppressed:           0 (0.000%)
112 Aug  4 06:52:12 pfsense.localdomain barnyard2[69002]: ===============================================================================

select statement failed because the syntax issue.

mysql> SELECT ref_system_id, ref_system_name FROM reference_system;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ref_system_id, ref_system_name FROM reference_system' at line 1

mysql> SELECT `ref_system_id`, ref_system_name FROM reference_system;
Empty set (0.00 sec)

The barnyard2 patched this 2 years ago in their Github repo. It seems that pfsense use old barnyard2 code.

[rickyzhang]

I also tried to replace maria db with mysql.

254 Aug  4 07:42:07 pfsense.localdomain barnyard2[85723]: ---------------------------- +[ Signature Suppress list ]+
255 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Barnyard2 spooler: Event cache size set to [8192]
256 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Log directory = /var/log/snort/snort_mvneta132940
257 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: INFO database: Defaulting Reconnect/Transaction Error limit to 10
258 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: INFO database: Defaulting Reconnect sleep time to 5 second
259 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Initializing daemon mode
260 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Daemon initialized, signaled parent pid: 85723
261 Aug  4 07:42:09 pfsense.localdomain barnyard2[85723]: Daemon parent exiting
262 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: PID path stat checked out ok, PID path set to /var/run
263 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Writing PID "85777" to file "/var/run/barnyard2_mvneta132940.pid"
264 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: database mysql_error: Authentication plugin 'caching_sha2_password' cannot be loaded: Cannot open "/usr/local/lib/mysql/plugin/cachin    g_sha2_password.so"
265 Aug  4 07:42:09 pfsense.localdomain barnyard2[85777]: Barnyard2 exiting

The whole barnyard2 is not tested. It should not release.

[NogBadTheBad]

The barnyard2 code is old all @bmeeks did is port it to pfSense.

With the next major Snort release I highly doubt will include barnyard2.

https://forum.netgate.com/topic/143538/barnyard2-and-mariadb

[rickyzhang]

I see. I will stop using Barnyard2.