Sorry if this is a bit of a broad question, but are there any best practices regarding accessing a remote MySQL via remote desktop versus locally through some other method? The goal here is to access a MySQL instance set up on a Windows 2008 r2 server and pull data mainly into Microsoft Excel. If you were to set this up, how would you do it (securely) and why?
Most MySQL installations come with networking disabled by default, e.g the my.cnf contains a line similiar to
bind-address = 127.0.0.1 which limits the network access to localhost for security reasons.
If you choose to enable networking by altering this line you need to make sure that you reconfigure all your MySQL users, to distinguish between local and remote MySQL users. You'll want to make sure all your remote users also have
REQUIRE SSL in their GRANT statement to enforce encryption for all remote connections. MySQL has a step-by-step setup for secure remote connections here.
On most (all?) linux based webservers MySQL remoting is disabled and the commonly accepted way to open up a remote MySQL connection here, is to tunnel SQL through SSH, so MySQL can be left unchanged and configured to allow only local connections. This is also possible on Windows Servers, but of course only with the added overhead of installing an SSH server (for example via Cygwin) first.
I would personally prefer to use the SSH variant as it allows MySQL to be left unchanged - you don't need to mess with your users & permissions inside MySQL. Also as a matter of personal opinion, I would think opening up MySQL as a protocol over the network is widening the attack surface of the server to a greater extent, then having SSH running does - especially if you are using key based authentification with SSH.
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.