SQL Server on AWS EC2

 Introduction:

Here we are going to discuss about SQL server on AWS EC2. First let me quickly explain what the difference between AWS offerings for SQL Server. There are two types of AWS services for database hosting

1. AWS RDS: Amazon Relational Database Service enables users to quickly setup their environment with almost zero maintenance activity. Amazon RDS itself it will take care of backups, patching and other maintenance activities including instance level optimization.
2. AWS EC2: Amazon Elastic Compute Cloud gives us more control and flexibility on environment. It’s like hosting a virtual machine on cloud but we should take care of maintenance activity.

Now this post is for the people who work on SQL Server on AWS EC2. We recently started moving SQL Server databases to AWS EC2 and RDS. We had some issues in accessing EC2 instances. Here we’ll demonstrate how to access SQL Server on AWS EC2 from a remote location using SSMS.

Connecting to SQL Server on AWS EC2

As I mentioned EC2 instance is nothing but a virtual machine hosted on amazon cloud. In our case it is a Windows Server 2012 Now we’ll see how to access / RDP EC2 instance from your local machine. One more thing we should remember is we cannot directly access / RDP the cloud instance, we need to go through a Jump Box.

Collect the Details:

AWS User Name: Ex: lj78787

AWS Password: ******

Token: Token provided by the company

Local PC Port: Ex: 5555

Jump Box IP Address & Port: Ex: 20.125.221.196 & 22

EC2 instance IP Address & Port: Ex: 20.122.233.200 & 1433

Download Putty:

Download Putty.exe from here

Configure Putty Settings:

Let’s understand what putty will do. It actually constructs a tunnel between source and target machines via jump box. If we configure Putty tunnel between Source Port: 5555 to Target Port: 9999. Once the tunnel connected from the next second on source port number 5555 represents the target port 9999.

Ex:

On Target machine SQL server is running on port 1435. We have configured a Putty Tunnel from local machine port (5555) to Target machine port (1435).

Now from our local machine if you want to access the target machine SQL Server we can simply access like “localhost,5555” this connects to the SQL server running on target machine.

Steps to configure Putty:

1. Run Putty.exe

Fig: 1

2. Give Jump Box IP address (20.125.221.196) at Host Name and default port is 22. Also give Saved Sessions as “EC2SQL”

Fig: 2

3. Next go to Data tab under connection
4. Give Auto-Login user name as “lj78787”

Fig: 3

5. Next go to Tunnels under SSH:

Source Port: An open port on your local PC (This port should not be assigned to any other services)

Ex: 5555

Destination: EC2 Windows Server IP and SQL Server port

Ex: 20.122.233.200:1433

Fig: 4

6. Click on Add button then it looks like below:

Fig: 5

7. Go back to the tab session and click on Save, so the session get saved into saved sessions. From next time we need not give all these details, we can simply select the saved session and load the session details. Once it is saved click on Open:

Fig: 6

8. It opens a command window and prompt for the password:

Fig: 7

9. Give the password: In our case it is password + Token number. On a successful connection we can see the output as below:

Fig: 8

Connecting to SQL Server on AWS EC2

We have successfully made a tunnel between source (Local PC) and target (AWS EC2) via a jump-box. Now we’ll see how to access SQL Server installed on AWS EC2 Windows server. We have already mapped port from source to target SQL Server.

Source: 5555

Target: 1433

On your local PC open SQL Server Management Studio and give connection string as “Localhost,5555”, authentication “SQL Server”, UserID and Password.

Fig: 9

Fig: 10

That’s it we have successfully connected from our local machine to SQL Server on AWS EC2.

Troubleshooting Connectivity issues

It’ll be tricky to troubleshoot things when we face connectivity issues to SQL Server on AWS EC2. We made a list of things that can quickly help you to resolve the connectivity issues. Here is the list:

Source (Local PC):

• Check the source port is opened and working fine. TELNET the local IP with the source port. Ex: TELNET 12.116.118.223 5555 or TELNET localhost 5555.
• Run putty.exe as “Run as Administrator”.
• While connecting through SSMS try different approaches. Ex: 127.0.0.1, 5555 or Localhost, 5555
• Make sure you are using the correct SQL username and password

Target (EC2 instance):

• Check SQL Server database engine is running on a correct port. We have seen one of EC2 sql server has been using 1434 and it was not able to accessible from remote locations as 1434 is a UDP port for SQL Browser and also it is the default port for Dedicated Admin Connection.
• Cross check the SQL port and SQL service are added to Windows firewall exception list under inbound rules.
• Check the target port is opened and working fine. TELNET the EC2 instance IP with the SQL port. Ex: TELNET 20.122.233.200 1433.
• Make sure SQL Server is configured to allow remote connections.
• Disable Windows firewall and try to connect if it got succeed it means Windows Firewall is blocking the connections.

AWS Console:

• Cross check that AWS security groups are properly configured
• Make sure that all required ports are added to inbound rules at AWS console
• Make sure EC2 instance is in the correct subnet (Internal / External)