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.