SSH not working

@ktottenATG

I just installed Sequel Pro back on my MBP and setup a stock installation of MariaDB (MySQL) using a bind address of 0.0.0.0 on a demo Droplet.

So bind-address looks like bind-address = 0.0.0.0.

I setup the same ufw rules that I provided you with:

ufw default deny incoming \
&& ufw default allow outgoing \
&& ufw allow 22/tcp \
&& ufw allow 80/tcp \
&& ufw allow 443/tcp

I then added the IP-Specific rule to only allow my IP to connect on port 3306.

ufw allow from MY_IP to any port 3306

Where MY_IP is my IP address (that of my VPN, so that I have a static IP).

When connecting to MySQL through Sequel Pro, my configuration looks like this:

http://oi68.tinypic.com/309seop.jpg

When testing the configuration, I get a success message. When trying to log in and out via SSH and using Sequel Pro over SSH, I’m not seeing any blocks occur, nor denial of access. I am using an SSH Key to login, though it prompts me for the key passphrase as expected and logs me in without any issues.

…

If you’re not using Sequel Pro via SSH, and instead using Sequel Pro’s Standard Connection method, you will need to add another user.

For example, if I’m connecting from my VPN IP, it’s not allowed to connect, so I need to give access to my IP by creating a second user.

grant all on dbname.* to 'dbuser'@'MYIP' identified by 'mypass';

Where MYIP is my VPN IP. So I end up with two users.

'dbuser'@'localhost'

and

'dbuser'@'MYIP'

The two logs (terminal and sequence-pro) look quite different to me.

The connection refused message by the destination in the Terminal attempt indicates that SSH isn’t even running (or configured for a different port) on port 22. I.e. nothing is serving that port.

With the other you are getting a connection (i.e. the ssh server answers), but the authentication fails.

What do you see if you telnet to the machine on port 22?

telnet 138.197.154.119 22

Ideally you should see a prompt saying OpenSSH and the version number. You can’t do more with Telnet but it indicates that the server is at least up and answering.

@ktottenATG

connect to address 138.197.154.119 port 22: Connection refused

Normally boils down to the firewall blocking your connection. This can happen for a few reasons, one of which is excessive login failures.

From what I recall, most of the one-click images for Ubuntu are setup with ufw, a firewall that acts as an overlay to iptables and simplifies the process of setting up most rule types.

If you’re blocked, the only way to get back in is via Console which you can access from the DO CP.

Click on the name of the Droplet in question. From the left side menu, click Access. Now click on the button that says Launch Console.

You’ll need your root password to login. If you only deployed with SSH Keys, the root password is disabled – in such a case, you’re effectively locked out of the server as the Console doesn’t allow pubkey authentication.

If you do have a root password set, you’ll be prompted to enter it in. You won’t see anything when entering the password, so you’ll need to type it in carefully. Once you have, hit enter.

Once logged in, you can run:

ufw disable

That’ll turn ufw off. Keep in mind, it’s not really recommended to run without a firewall active, so the best thing to do would be to simply reset the firewall and then setup new rules.

Once the firewall is disabled, try to login via SSH once again. If you’re able to login, I would use the following to setup the firewall.

Double-Check ufw is disabled:

ufw disable

Reset the rules:

ufw reset

Setup Default Policies:

ufw default deny incoming && ufw default allow outgoing

We’re setting the default policy to deny any incoming connection. This is preferred as we want to setup a deny all, except what we explicitly allow type policy. This is the first part.

Now, we’ll add rules for SSH (Port 22), HTTP (Port 80), and HTTPS/SSL (Port 443). These are the ports we want to allow connections on. This ensures that only these three ports are open, no more, no less.

ufw allow 22/tcp && ufw allow 80/tcp && ufw allow 443/tcp

Now, I noticed that you’re using Sequel Pro, which means we need to open another port, and that’d be 3306 as that’s the port MySQL/MariaDB communicate on by default. The issue here is that with the base configuration for MySQL (which is what DigitalOcean uses), remote access to MySQL is not enabled, so you need to enable it.

You should have this file:

/etc/mysql/my.cnf

We need to open that file using nano and find bind-address. Normally this is set to 127.0.0.1, which is localhost. If you try to connect with bind-address set to that IP, it’ll fail.

You need to change bind-address to either 0.0.0.0 or the IP address of your Droplet (the IPv4 IP) and then restart MySQL.

service mysql restart

To add MySQL to the firewall, we’d issue one more allow command, like so:

ufw allow 3306/tcp

With SSH, HTTP, HTTPS, and MySQL now added, we can enable ufw using:

ufw enable

It’ll ask you to confirm – do so – and logout, then try to login again. Then try to login with Sequel Pro, etc.

…

Note: It’s really not a good security practice to allow open access to port 3306 as is being done in the above guide. Ideally you would restrict access to a single IP, or set of IP’s (if multiple people need access). Allowing open access, such as what is being done here allows anyone to attempt to login to MySQL on your server, whether the user(s) exist or not.