Secure a hosted MySQL DB

Question

We manage an internet based SaaS app which has a MySQL DB as its backend DB. It is a Windows 2008 R2 server running MySQL 5.5/Apache 2.2 I have started using MonYog to monitor the health of the DB Server and get regular (hourly) emails informing me that the number of refused connections is high. Looking in the logs for the DB I can see constant login attempts as there are 'Access denied' entries in the log. I suspect someone is trying a brute force hack attempt.

Question is, how can I best secure this server. At present I have port for DB open on firewall and cannot restrict that usage to a 'white list' of client IP addresses as I have developers working on the system who do not have a fixed IP address (working on the road, at home etc). The main app (web site) is on the same server so that connects as a local host only user so I guess that is pretty secure.

The challenge is the remote techs who need direct DB access for coding procedures etc.

Is there something I am missing in being able to lock the server down more and stop these constant hack attempts into the open TCP port of the DB?

Thanks Mark

Answer 1

Enable SSL (Do this in addition to options below)

Besides the options listed below you should also enable SSL on your MySQL database. Otherwise usernames/passwords will be sent over the internet in plaintext. Based on your use case if a developer is sitting at a Starbucks and connects to your database (without SSL) then anybody could eavesdrop and steal the credentials to login.

You can read more about setting up SSL on MySQL here: http://dev.mysql.com/doc/refman/5.5/en/ssl-connections.html

Option 1 - Bastion Host

One approach to allow you to lock down access to your database is to setup a bastion host. The bastion host could be setup to allow connections from any IP address and your database would be setup to only allow connections from the bastion host. Basically it's like a proxy server.

The standard setup for it is to use a *nix machine with each developer having SSH access with key based authentication but no command execution privileges. Each developer could then SSH to the bastion and forward a port to the DB server but not execute anything else.

Securing the bastion becomes much easier than securing the DB server itself as authentication is done via SSH keys (vs passwords) and thus cannot be brute forced. Also, it doesn't matter that your MySQL server is running on Windows, only the bastion host needs to be *nix.

Option 2 - fail2ban (equivalent)

If you were running on a *nix OS I'd recommend running fail2ban. It's a daemon that scans your log files for unusually activity and sets up IP block rules based on it. For example you can configure it to blacklist IP addresses that are repeatedly trying to login to a website by having it scan the access logs.

You mention that your server is running Windows so fail2ban itself won't run on it (it's *nix only) but it looks like some other folks have tried similar things. I haven't tried either of these approaches so can't vouch for them but you could try them out:

1. https://serverfault.com/questions/27757/does-fail2ban-do-windows
2. https://serverfault.com/questions/43360/cygwin-sshd-autoblock-failed-logins

In both cases I think you'd have to tweak it to work with MySQL (it's setup to protect sshd).

Option 3 - JackDB

Full Disclosure: I'm the founder of JackDB

If your remote developers are connecting to your database to query/analyze/debug it via a database client then then you could have them use JackDB. It's a database client that runs entirely in your web browser.

Once it's setup, you could set up your firewall to only allow remote access from JackDB's IP address (it's a single static IP address) and your developer's could then use it to access an query your database. Your DB server would then be firewalled from the rest of the internet.

Note that this will only be a viable solution if your remote developers will only be executing SQL commands against the database and not remotely developing against it. JackDB allows you to run any SQL (SELECT, INSERT, UPDATE, DELETE, etc) but it does not allow for programmatic access for custom programs (it's a database client, not a driver interface).

You can read more about it and try it out here: http://www.jackdb.com/