Linode Forum

[markunit]

We are currently testing with Linode and trying various options.

Our current setup is, We use a shared server (at lunarpages) to host the website and basic MySQL for account info, but host our main MySQL databases remotely at xeround as we need to allow our customers remote access to their database with a desktop application and need good performance and bandwidth.

My question is, is it safe, good, fast to allow remote access to our own MySQL server on Linode or is it better to keep offloading the SQL storage, load and security to an external server and keep each area separated.

The load is steady and about 20GB per month.

Any comments for or against the combined or separate setup ?

Thanks

[hoopycat]

Decent desktop apps (at least, MySQL Workbench) will set up and use ssh to connect to a remote database server. This works out pretty well, and means you can leave MySQL bound to localhost. It's remote access for anyone who passes the ssh test.

[obs]

If your users are on os x then sequelpro is a good alternative which also uses SSH.

If you don't want to give your users SSH access then you can set up openvpn (check the linode library) and set mysql to listen on that ip then they can use the standard mysql protocol over the secure vpn connection. This also allows you to host other internal services on the same connection effectively creating your own intranet.

[markunit]

Thanks for the comments so far.

Sorry, I should have given more details about our software.

Our customers never see or access the MYSQL database with anything but our own software.

They use our own scheduling software (http://www.scheduleit.co.uk) which stores their data in the cloud so they never know where, who or how its stored, they just know its in the cloud and they can access it with our custom browser service or with the desktop software from any location.

We can store their data in any location and format but we are trying to understand for us, the provider of the service, if keeping it local on our servers (at Linode) of offloading it (to xeround), is a better solution for our support and admin, and reliability for our customers.

I like the separate setups in case we have server problems the data is safe, but need to understand any benefits of having it together.

[Piki]

If your Linode will need to access your database, then you'll get better performance by keeping it on the same Linode. You'll also save on bandwidth if you do this.

You can also have two Linodes on the same datacenter and setup your database on that datacenter's private network so you don't have to worry about bandwidth.

You can also utilize Linode's backup server for daily or weekly backups. That way, if something goes wrong, you can easily restore.

The downside is that you have to handle configure the security yourself. Since we don't know your specific needs, we wouldn't be able to offer advice on how to configure your iptables, except to block everything and allow only what is absolutely necessary.

The only other downside is if your database stores a lot of data, but having two Linodes on the same datacenter can help with that.

[markunit]

Thanks for the good info.

We would need to give full unlimited access to our MySQL as we have many hundreds of customers worldwide with our desktop application that need access. And they can move around if accessing whilst working in different locations. So blocking any IP range would not work for us I dont think.

Each customer does have their own database with their own username and password that our desktop application uses, but we wondered how safe this is to allow this openness.

[obs]

Password authentication is just asking for a brute force attack.

Modify your app to either use SSH tunnels or a VPN they can use certificates/key pairs to prevent brute force attacks.

[Piki]

You don't have to block per-IP. If every database is on the same port, you can allow all IPs on that one port.

If you block all ports except what you actually need open then the only security you have to worry about is in the databse yourself. Since we don't know the setup of your database or what database software you'll be using, we can't say how safe it will be. You'll have to keep users and privileges as separate as possible, and do whatever possible to keep people from injecting malicious code into your database.

[markunit]

Thank you all for this great feedback.

Our test setup might be:

Our desktop program, on a customers PC, connects to our Linode website via SSL, identifies the customer account and requests its MySQL connection string to their database all via PHP, which is returned if the account details are correct (e.g customer name, password, etc).

Then the connection string is used to make the connection to the MySQL server at Xeround. The connection string contains a unique and very long user name and password as it never needs to be typed and is never seen by the end user/customer.

The long user name and password should limit the chance of brute force attacks succeeding over a realistic time frame.

Adding SSH for the MySQL connection would prevent SQL snooping, any other thoughts ?

How can we secure it further ?

[Piki]

If you have the long user name/password be randomly generated and have it set to expire after a set time, that may help with security. You might be able to have a system similar to WPA for wireless where as long as the client software provides the correct credentials, the server can generate and assign a temporary authentication token, and the client software can request a new one when the old one is going to expire.

You can use ssh keys and turn off password logins in ssh. One suggestion is to include the public key for a limited user in your software, and have it generate the keypair. The public key can include the username for your user in it's file name, so when it gets uploaded, you can have a cron job scan every few minutes for new keys, and will know (as per the file name) who's authorized_keys file to add it to. The option to generate a new key pair would be needed, just in case they delete it by accident or need to allow access from another machine.