Snort as an IPS protecting Client Data held in MSSQL and MySQL databases in the event of a compromise to a seperate Application Server.

Why?

There are really 2 questions here... Why is it necessary and Why this particular solution... Digital Ocean has a good article on the structuring of a Web Application. in the situation applicable here, "2. Separate Database Server" was the simplest option to get to market quickly. Client Data is stored on a private LAN in an MSSQL database.

Why is it necessary?

In this instance Snort is just one layer of security. It is key that Snort's control interface is "out-of-band". A compromise to the web application (however unikely) must not (and will not) give access the Snort control interface.

Other layers:

Unprivileged user, but as features increase the user is ultimately granted access to (sensitive) Client Data and only the application is left to ensure that that is not leaked
Prepared statements to avoid first order injection attacks and strip_tags() and htmlentities() to prevent second order injections

Snort is Best Practice says an extra layer of protection not "administrable" from the Application Server is advisable to slow an attack / breach of the App server to protect from wholesale data theft and/or corruption.

Why this solution?

There are ?arguably? better solutions (see GreenSQL, an SQL proxy), "Best Practice" in this case is likely a REST API layer between the Application and Database servers). These are more complex / expensive - in the case of the API there would be a second development stream. Snort with regex in IPS mode can add most of the protection, but without the complexity and provide an Intrustion Detection function allowing rapid first response

Issues to consider

This is from an FCA data security fact sheet: from an FCA fact sheet

This plan does have some potential issues. Each is considered below particularly with a view to ensure

A human is notified of a potential breach or attempted breach as soon as possible to trigger a pre-agreed out of band response
Wholesale theft of client data is prevented immediately EVEN IF the application server is breached since the requesting query is paradigmatically blocked

Queries are not "understood", the solution must fail safe and thus block everything on port 1433 (MSSQL) and port 3306 (MySQL). Each acceptable packet - every SYN, ACK, Login and Query packet will need to be specifically allowed.
```
        reject tcp 0.0.0.0/0 any -> 192.168.66.214 1433 (msg:"MSSQL ANOther block"; sid:19900)
        reject tcp 0.0.0.0/0 any -> 192.168.66.219 3306 (msg:"MySQL ANOther block"; sid:29900)
```
This will effectively block the use of content encryption AND compression (the content won't pass regex checks). Running a tunnel to a nearby device will still data to be encrypted in transit.
Packets containing queries may be fragmented, but Snort can re-assemble these to ensure a good match is possible, also due to the default block any failure state is still safe.
Piggy backing and Injection - All known acceptable queries can be matched and passed using snort's pcre function, but consider
```
pass ...snip... prce: "/SELECT allowedcolumn FROM allowedtable WHERE id = '[A-Z\-]{5,15}'/
```
if this were sent
```
SELECT allowedcolumn FROM allowedtable WHERE id = 'AAA-BBB' OR 1
```
or this
```
SELECT * FROM allowedtable ; SELECT allowedcolumn FROM allowedtable WHERE id = 'AAA-BBB'
```
both would allow wholesale theft of client data. It is clear that the beginning "^" and end "$" must be matched. From my investigation there are 8 data characters in an MSSQL packet BEFORE the SQL statement and 0 at the end, so this will prevent both the above scenarios
```
pass ...snip... pcre:"/^[\x00-\xFF]{8}SELECT allowedcolumn FROM allowedtable WHERE id = '[A-Z\-]{5,15}'$/
```

The paradigm considered here doesn't block the data flow, it blocks the request for the dataflow. This is a potential short coming, should the data be coherced to egress it would not be blocked... possibly tc could be used to rate limit egress and automated action should be taken on excessive data flows...

A basic draft of the network topology

rough diagram of the nework topology to support this

The device used to run Snort

Per guidelines for running Snort in IPS mode, 3 ports are necessary. 2 to create the "cable break" and 1 used for out-of-band admin. This (solid state) device is available for less than £300. It takes about 2 hours to set up and then the time to write the regex rules to match the allowed queries. Mi19L has 4 NICs

Issues once in production

Performance

Whilst everything worked at first, when placed under load performance topped out at a few hundred kbps. I found that I needed to make these changes:

/sbin/ethtool -K eth1 gro off
/sbin/ethtool -K eth1 lro off
/sbin/ethtool -K eth2 gro off
/sbin/ethtool -K eth2 lro off

see http://seclists.org/snort/2014/q4/423