Nat – Connecting a server in a private subnet from a server in the public subnet

The problem:

standard AMI instance not working correctly as a NAT instance

If you try to use a standard AMI instance as a NAT instance it'll have the same symptoms as described in the question above:

• SSH connection to the NAT = OK
• SSH connection to private subnet instance = OK
• ping to/from private subnet instance = OK
• private subnet instance outbound calls = FAIL (even with valid route tables/security groups)

The hard solution:

To get a standard AMI instance working as a NAT instance you need to customise it:

• modify the iptables as shown here
• ensure IPv4 forwarding is enabled and ICMP redirects are disabled as noted here

The easy solution:

use a Community amzn-ami-vpc-nat instance already configured for use as a NAT instance

For most people (who are using the NAT instance for admin connection purposes) a customised NAT instance is simply unnecessary.

AWS provides standard instances configured for NAT use (ie modified as described above) as community AMI instances.

• click Launch Instance in the EC2 control panel
• in Step 1: Choose an Amazon Machine Image (AMI) click the Community AMIs selector on the left
• type amzn-ami-vpc-nat 2017 or more simply nat 2017 (note below) in the search input box
• launch your NAT and ensure your route tables + security groups are correct - see here

Note:
The reason you include the year in the Community AMI instance search is that AWS keeps all the old NAT AMI versions (21 at the time of writing, going back to 2013) - which mostly include a year in the version number
...it's best / easiest to simply choose the latest version.

Connect to db in private subnet instance via NAT with MySQL Workbench

Create a mysql user who can connect remotely:

• by default the root user cannot connect remotely

• create a user who is allowed to connect from your IP address (or use % which means any address)
  -- connect to the db instance in the private subnet via SSH
  -- log into mysql as root and type password when prompted:
  mysql -h localhost -u root -p
  -- run the following query to create a user
  CREATE USER 'username'@'XX.XX.XX.XX' IDENTIFIED BY 'mypassword';
  -- ensure that the user you've added has all privileges required:
  GRANT ALL PRIVILEGES ON *.* TO 'username'@'IP' IDENTIFIED BY 'password';

• ensure that the users you are adding are not duplicates in either username or password
  -- view the user table in terminal with the following mysql query:
  select * from mysql.user\G;
  -- it's a good idea to delete the anonymous user for both security and potential user collisions: https://stackoverflow.com/questions/10299148

• when complete run query: FLUSH PRIVILEGES;

Set up your security groups to allow remote MySQL connection via the NAT instance:

• with WorkBench you're SSH tunnelling up to the NAT then connecting via port 3306 to the db instance

• ensure that the IP you're connecting from is allowed to connect to port 3306 in AWS security group
  -- the NAT instance should have SSH(22) inbound from your IP address
  -- the NAT instance should have MySQL(3306) outbound towards the VPC address range (eg 10.0.0.0/16)
  -- the db instance in the VPC private subnet should allow inbound MySQL(3306) from the VPC private IP range (eg 10.0.0.0/16)

Set up Workbench to connect to your db instance via the NAT instance:

• open WorkBench
• create a new connection and give it a name (eg my_vpc_db1)
  -- choose 'Standard TCP/IP over SSH' as connection method
  -- SSH host is the public IP of the NAT intance e.g. XX.XX.XX.XX
  -- SSH Username = ec2-user
  -- SSH Password is blank (clear this if necessary)
  -- SSH Key File = browse to location of the key_pair.pem file
  -- MySQL Hostname = private IP address of the db instance in the VPC
  -- MySQL Server Port = 3306
  -- username = the name you just added in CREATE USER
  -- password = just added in CREATE USER