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 for this entry), but was never able to connect.  So I tried using the command prompt and entering: telnet 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
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  (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: 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: (I don't recommend using the, 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