Remote Access SQL Server Express Database on AWS EC2

For startup business and low volume web sites, Amazon provides a cost saving option to run your web site on EC2 instance. I'll show you that you can also install SQL Server Express on the same EC2 instance. Having a data source will allow users to sign-up on your web site and be more engaged to your content.

Microsoft has changed the rule to use SQL Server Express, which allow SQL Server Express server to be used in small or medium websites. This is quite a successful option for many sites with database sizes under 4GB limits. Installing SQL Server Express on EC2 is more cost saving than RDS. It also gives you direct access to your database server, so you can change features or upgrade your database server freely.

The reason to have remote access to your SQL Server instance is to save disk space and memory on your EC2 instance that's hosted on Amazon cloud. It's critical since you pay more monthly fees for more disk space and memory.

You can download and install SQL Server Management Studio (SSMS) on your EC2 instance, but there are some downsides. The SSMS installer itself is about 1GB. Comparing to the t2.micro instance which only has 30GB space total, it's quite large. Also, when you run query on SSMS, it will create caches for query results. This will eat quite a lot of space as well. The SSMS UI will also slow down your server significantly on a 1GB or 2GB memory EC2. There're quite a few things you need to do to allow remote access to your SQL Server Express from your own computer. I'll show you step by step.

1. Remote Access to EC2

You need to have the key pair file (.pem) that was created with your EC2 instance (this can only be downloaded once). You need this file to get the password for your EC2 instance.

Login to AWS -> Go to EC2 Dashboard -> Click “Running Instances” -> Select the EC2 instance -> Click “Connect” -> Click “Download Remote Desktop File” and save the file -> Click “Get Password” -> Select your keypair file -> Click “Decrypt Password” -> Copy and kept the password

2. Logon to EC2 and Install SQL Server Express

Make sure you have the remote desktop feature installed, double click the remote desktop file downloaded, then enter the admin password. Change the IE security setting to allow file download, download SQL Server Express and finish installation steps (remember to choose Mix mode for authentication). If you only chose Windows Authentication mode during install, you need step 3 to turn on Mixed mode.

3. Turn on Mixed Mode Authentication

In EC2 Remote Desktop window, run SQLCMD.exe in windows command window,

--Login Database with windows authentication
sqlcmd -S servername

--change authentication mode to mixed
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Reboot your EC2 instance, remote desktop again.

--Login Database with windows authentication
sqlcmd -S servername

--change sa password
USE [master]
ALTER LOGIN [sa] WITH PASSWORD=N'<strong password>'

Exit SQLCMD and use new password to login,

sqlcmd -S servername -U sa -P <new password>

4. Turn on Remote Access to database server

In EC2 Remote Desktop window, run SQLCMD.exe in windows command window, run following command to login,
sqlcmd -S servername -U sa -P <sa password>

--allow SQL Server remote access
EXEC sp_configure 'remote access', 1 ;

5. Enable Port 1433 inbound request on firewall

Add a rule in firewall setting on EC2 instance to enable inbound request to port 1433 (which used by SQL Server Express)

6. Enable TCP/IP for SQL Server Express

Open "SQL Server Configuration Manager" -> Select SQL Server Network Configuration -> Select Protocols for MSSQLSERVER -> Right Click TCP/IP -> Choose “Enable”

7. Enable TCP 1433 inbound for AWS security groups

Open EC2 instance list, select the EC2 instance with SQL Server Express, select "Inbound" tab, click "Edit" button, now you can add a new Inbound rule for port 1433.

8. Test Remote Login

Get public IP of your EC2 Instance. Use SSMS on your local computer, using EC2 public IP and sa password to login SQL Server. If it's not working, try add or remove “\SQLExpress” instance name after public IP address.

Now everything is done. Your hard work has paid off.