Monday, 8 October 2012

MySql Access from Windows

The MySql .Net connector is a straight-forward way of connecting to MySql databases which works much in the same way as System.Data.DbConnector which means that it is easy to port an application from Sql Server to MySql. However, the connection string takes a small tweak to use the standard fields like server= and database= (I think!?) and then you need to connect the web server to the database server in the most secure way. You will need to do the following:

  1. Create/establish your MySql server (I use the one on Ubuntu so it is nice and slick) - in my case this is on Amazon Web Services.
  2. Firewall your database server so it only allows ssh access (I change the port to be something other than 22 to avoid it getting attacked). Also, allow access to port 3306 (MySql) but ONLY for your web server's IP address.
  3. If you want to connect directly to the database from your local PC after you have firewalled it then tunnel the connection in using ssh: ssh -fCNp @ -L ::3306 -i .pem and then connect to 127.0.0.1: with MySql workbench (or whatever tool you are using).
  4. Edit /etc/mysql/my.cnf on your db server so that the MySql daemon is bound to all interfaces by setting bind-address to 0.0.0.0 You can only set it to ALL or a single ip address and since often you need it to be bound to 127.0.0.1 for tunnelling in, you have to go for the ALL option. If your db server has multiple interfaces and you don't want it bound to all, then use a firewall like firehol to lock down the interfaces from mysql traffic.
  5. You will want a user that is locked down to only access the database for your web application - DO NOT USE ROOT. If you have multiple apps, I recommend a different user per application so that if one was hacked, the damage is limited. These users should not be able to do anything "out of band" like drop tables, create databases etc and ideally, if your app can work with only stored procedures, it should only need execute permission and nothing else.
  6. You can try a simple connection from your web app, but if that doesn't seem to work, download MySql workbench and attempt to create a connection to the MySql server. This will tell you whether the connection is valid or not and should give you some clues to what might be wrong. For instance, you might need to open outgoing traffic on the firewall to allow Windows to talk to the MySql server - I can't remember if I had to do this or not.
Post a Comment