Pfsense logging to security onion snorby mysql how to



  • So, you have PFSense and Security Onion setup and you want to feed your snort IDS alerts from PFSense to Snorby? So did I so I went looking for instructions on how to do this; I didn't find any :(. After a couple of days and a lot of coffee and a healthy dose of OCD I finally had success! I have Snort events populating in Snorby from my pfsense box!

    Here is how I did it.

    #######################################################################################################################
    !!! DISCLAIMER !!!

    Now, this likely isn't the best way to do this (but it worked) and if there is anything you think I should do different please let me know by pointing out my screw-ups, how stupid I am, and how it could (should) have been done better.

    My security onion box sit's off on it's own DMZ with no direct access and certainly no outside access to it. I can not vouch for the security of the instructions below, only that it worked. Please let me know if I can do something better.
    #######################################################################################################################
    PFSense 2.1.5-RELEASE

    Step 1:
    login in (SSH) to your security onion box and stop processes.```
    sudo service nsm stop

    
    Step 2:
    Then to mysql and create a new user with ALL PRIVILEGES on the snorby database with a password.
    
    

    mysql -uroot

    GRANT ALL PRIVILEGES ON snorby.* TO 'pfsense'@'your_pfsense_ip_here' IDENTIFIED BY 'your_mysql_user_pfsense_password_here';

    
    Exit mysql.
    

    exit

    
    Step 3:
    Modify the mysql config to accept external connections.
    
    

    sudo vi /etc/mysql/my.cnf

    vi [http://www.washington.edu/computing/unix/vi.html](http://www.washington.edu/computing/unix/vi.html)
    
    Find line #47\. It should read "bind-address  = 127.0.0.1"
    Make this line look like this "# bind-address  = 127.0.0.1"
    NOTICE THE "#".
    
    Create the necessary firewall rules on Security Onion. Specifying which source IP's are allowed here would help improve the security as well.
    

    sudo ufw allow 3306

    sudo ufw allow 8888

    
    Step 4:
    Restart mysql```
    sudo service mysql restart
    

    Step 5:
    Log back into mysql as root```
    mysql -uroot

    DELETE the sig_reference.```
    DELETE FROM sig_reference;
    

    Step 6:
    Install and configure stunnel on Security Onion. Be sure to use the same ports and everything.
    https://www.digitalocean.com/community/tutorials/how-to-set-up-an-ssl-tunnel-using-stunnel-on-ubuntu

    Enable SO_KEEPALIVE to help keep the tunnel up.

    sudo stunnel -O r:SO_KEEPALIVE=1
    

    Step 7:
    Install and setup stunnel on PFSense. Go to System > Packages > Available Packages tab > install stunnel.
    then
    Services > STunnel > Tunnels tab > Add New Item button (+) > Description: mysql tunnel > Listen on IP: 127.0.0.1 > Listen on port: 3306 > Certificate: Default > Redirects to IP: Security Onion box IP > Redirects to Port: 8888 > Outgoing source IP: IP address of your firewall's appropriate (Security Onion server facing) interface.

    Save

    Step 8:
    BEFORE starting the nsm processes:
    Log in to pfsense > Go to Services > Snort > for each Interface desired select edit > Barnyard2 tab > Select MySQL Database Output Settings > Check box "Enable MySQL Database" >
    Database Host: 127.0.0.1
    Database Name: snorby
    Database User Name: pfsense
    Database User Password: Whatever you set at Step 2 "your_mysql_user_pfsense_password_here"

    Save

    GO back to Services > Snort > And enable the desired interface's Barnyard2 service.

    This will fill in```
    sig_reference

    
    Step 9:
    After the initial transfer is complete (watch your network graphs or from the command line on your security onion box (```
    sudo tcpdump -i lo port 3306
    ```…be patient...) no longer seeing traffic traverse the interface over port 3306; restart your nsm processes on your security onion box.
    

    sudo service nsm start

    
    :)
    
    Check your timezones on both servers and be sure they match.
    
    Afterwards I've found it helpful to mark the checkbox in PFSense > Snort > edit internface > Barnyard2 > MySQL Database Output Settings > Disable Signature Reference Table.
    Doing this has prevented errors which cause the Barnyard2 process to stop.
    
    Hopefully this gets you what you want. I'll post more integration steps for PFSense and Security Onion as I learn them. If you have any questions or comments please feel free to let me know and I'll do my best to answer them. Like I said, I always want to learn more.
    
    -Dielan
    
    EDIT: My stunnel setup is problematic but it works fine without stunnel…tbc


  • Step 2 and 5 that call for mysql prompt first number 2 does not modify and rows and number 5 step says ERROR 1046 (3D000): No database selected

    What am I missing?



  • Since the last post, both securityonion and pfsense have had many major changes.  Please give feedback on using securityonion to trap syslog from pfsense.  Please assume pfsense is running > 2.3.1 and securityonion > 14.04.4, when giving feedback (which are the latest builds, as of this post).



  • I would also appreciate any insight into logging from pfsense to security onion now that snortby is depreciated from the security-onion iso.


Log in to reply