Sql-server – Firewall security and SQL Server

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/

SSMS uses port 1434 and the UDP protocol. This has to be allowed on the firewall, too.

Quote:

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

From here.