Sqlyog Job Agent Connection Security

[SMR Group]

Folks,

We've got a few web servers grouped as primary and secondary servers. Websites are hosted on multiple servers and failovers used should there be a network issue.

Currently the MySQL servers use named pipes and have TCP/IP disabled. The MySQL ports are also blocked by our firewalls.

I'd like to automate our MySQL data backups with SQLyog Job Agent, but am concerned about security. Obviously we're going to need to enable TCP/IP and a firewall port, but what would people suggest as the best way of making the connection so that data and username/password combinations (and the servers themselves) remain secure?

We're currently on Windows 2003 machines.

Thank you.

[peterlaursen]

1) You can use SSL encrypted connections!

Recent MySQL binaries (also for Windows) have been compiled with SSL. It must be enabled in configuration however!

http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

To avoid 'pitfalls' with configuration read this:

http://bugs.mysql.com/bug.php?id=25748

and do not be confused about the 'have_ssl' system variable – it is 'have_openssl' that matters!

http://bugs.mysql.com/bug.php?id=31265

Also note that a specifically SSL-enabled user is required for this (a MySQL user is either a SSL user or not – he cannot be both! Refer to GRANT syntax description in MySQL docs).

There are programs in practically every unix/linux 'dialect' to build SSL ceertificates. No need to pay for it!

Alsos note that SSL certificates will expire and need to be renewed!

2) Also SSH is an option with Windows.

The free OpenSSH package works fine on 2003 – however it is a little more tricky to set up on this system compared to 2K/XP.

A good resource: http://www.cs.bham.ac.uk/~smp/projects/ssh-windows/

(and if you ever get across a similar resource on OpenSSH and Windows Vista please tell me!)

3) Finally HTTPS is there too! I would prefer the two others for a permanent solution. But depends on performance and stability of your HTTP server.

[SMR Group]

Peter,

Thank you for the detailed reply, that's most helpful. I'll go through the options and decide which will be best for us, and the easiest to deploy.

It would seem that using the integral MySQL SSL encrypted connections is probably the ideal one to chose, although looking at the version number I think we'll probably need to upgrade our MySQL installs first.

[Author]

Posts