Connecting to SQL Express on Amazon AWS EC2

by Idaho Web Designer 9. September 2011 20:00

I've been playing with the Amazon Cloud servers just to see what they have to offer.  I thought I would set up a quick little SQL Express database and then connect to it remotely just to play around.  It turned out to be a bit of a pain and so I though I'd share what I did in order to be able to connect to it remotely using management studio. 

After the database was setup and running I made sure that it would accept remote connections (Connected to the Database in Microsoft SQL Server Management Studio -> Right Click Database Instance (localhost\sqlexpress) -> Properties -> Connections -> Make sure "Allow remote connections to this server" checkbox is checked) .  I then tried connecting using Port 1433 and my IP address (We'll pretend my IP is 20.10.20.105 for this entry), but was never able to connect.  So I tried using the command prompt and entering: telnet 20.10.20.105 1433 and still got nothing.  So the first obvious thing to me was that the port was not listening for my connections. I logged back in to the database and ran the command:

use master
go
exec xp_readerrorlog

on my database and found my database was listening on a dynamic port (Text read Server is listening on ['any' <ipv6> 48231].)

I logged on to the EC2 machine and opened the Sql Server Configuration Manager (Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager).  Then I opened SQL Server Network Configuration ->Protocols for SQLEXPRESS. Open TCP/IP by double clicking.  Then selected IP Addresses tab -> Scroll to the bottom under IPAll.  Here was my dynamic port reading TCP Dynamic Ports 48231.  Under it was a blank TCP Port.  So I added TCP Port 1435 (No rhyme or reason for that number).  I went back and rand the xp_readerrorlog and found that my database was now listening on any <ipv6> 1435 in addtion to the 48231 now.  I thought I was good to go.  NOPE!

I logged in the AWS Management Console and selected Amazon Elastic Cloud Computer EC2 at the top of the screen.  I then see my two instances listed by clicking on instances.  I clicked on the instance I was having trouble with and scolled to the bottom to find my Public DNS ec2-20-10-20-105.us-west-1.compute.amazonaws.com.  (If you don't see your instances, make sure you select the right region on the top left, mine defaults to East region and my instances are in the west). 

I now tried to connect with telnet by using: ec2-20-10-20-105.us-west-1.compute.amazonaws.com 20-10-20-105 1435.  Still nothing!  I was getting very frustrated at this point, but then I remembered the security groups.  Amazon has security groups under the AWS Management Console as well.  I clicked the Security Groups link on the left and opened the security group my instance was on.  At the bottom of the screen I saw two tabs "Details", "Inbound". I opened the "Inbound" tab and found some ports that were open.  I created a new Custom TCP rule with the port range of 1435 and Source: 0.0.0.0/0 (I don't recommend using the 0.0.0.0/0, it opens your port to every IP address, but I was just playing.  Open it for just your IP address if possible.)

I went back to my telnet and Wa La!  I was able to connect remotely. 

Tags: , ,

Amazon Cloud | ec2 | Database