Cannot connect to Amazon RDS with SSMS
Amazon allows you to run an Relational Database Server (RDS) that runs several of the common editions of Microsoft SQLServer. After setting up your RDS server/instance and launching it, you may have issues connecting to it.
Cannot connect to SQLServer – Error Message
Microsoft SQLServer has a very general message when you cannot connect.
It reads as follows:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible…
Within the Amazon AWS console, there is a screen for setting up your Virtual Private Cloud (VPC). Within that, one of the options is for the “Security Groups”. By default, you RDS SQLServer is open only to certain spaces within the Amazon world. To open it up to an external program, such as SQL Server Management Studio (SSMS) on your PC, you will have to add a security group or change the existing one.
Amazon VPC Security Group Default
The screen shot below show the original value. The blue square “dot” at the top shows the security group that I currently have selected, and the bottom half of the screen gives the details on four different tabs. It’s the Inbound Rules that need to be adjusted. By default, the source, in my case, was sg-0ee39c6b which was the default security group. I’m still not 100% sure what it represents, but it definitely doesn’t include anything outside of the Amazon data centers. (By the way, in this screen shot I’m in “Edit” mode.)
Amazon VPC Security Group Modified
The screen show below (not in edit mode), shows the change I made. I changed the “Source” from the security group-id to a CIDR of 0.0.0.0/0, which basically means any IP Address. You are basically defining firewall rules here. It would be best to only open port 1433, which is used by SQL, to avoid any other hackers trying to attack your server. A common technique is to open everything first, make sure it works, then starting closing the doors to everything except the minimum that are needed to be left open.
To open up for example, you home PC, use “What is my IP Address” in Google to get how the outside world views your IP Address, then convert it to a CIDR. Here is the URL of a handy online IP Address Range to CIDR converter. That utility only accepts a range of IDs (and the ending range has to be different from the starting range).