How to Connect to AmazonRDS from SQL Server Management Studio?
Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.
Although application connect to the database and works fine, it is important for DBAs or Developers to be able to query for data and to manage Databases and other objects places on AmazonRDS. There may be different ways to connect to Amazon RDS and manage or query data, but one of the easiest method and most popular and convenient way is to do it through well known tool which is SQL Server Management Studio(SSMS). We only need few basic details and then we should be good to connect to our database on AmazonRDS.
Firstly we need to create a security group from AmazonRDS web console, and then we must modify the DB instance to associate it with the security group. Once we are done with it, we can follow below steps to be able to connect to our database from SSMS
– On the Instances page of the AWS Management Console, we need to choose the arrow next to the DB instance to show the instance details. Make a note of the server name and port of the DB instance, which are displayed in the Endpoint field at the top of the panel, and the master user name, which is displayed in the Username field in the Configuration Details section.
– Launch Microsoft SQL Server Management Studio(SSMS). Then Connect to Server dialog box appears in the Server section “select Database Engine”. Now provide the server name of the DB instance then a comma(,) and port number. Example: EndPoint,Port
– Now choose SQL Server as authentication type as this is the only authentication method supported in AmazonDRS. Provide the user name and password and then click connect and you should be connected successfully, unless there are any port or firewall issues. Open a new query window and run few sample queries to make sure you can run queries.
Some of the known issues which you may face while trying to connect to AmazonRDS from SQL Server Management Studio are related to to your local firewall and the IP addresses you authorized to access your DB instance in the instance’s security group are not in sync. If you cannot send out or receive communications over the port you specified when you created the DB instance, you will not be able to connect to the DB instance. Check with your network administrator to determine if the port you specified for your DB instance is allowed to be used for inbound and outbound communication.or newly created DB instances, you must wait for the DB instance status to be “Available” before you can connect to the instance. Depending on the size of your DB instance, it can take up to 20 minutes before the instance is available.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2012
SQL Server 2008 R2
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings