Connecting to AWS RDS Instance from local SSMS
DB instance is the building block of Amazon RDS. It can contain multiple user created databases. We need to get access to database more securely. Security is always number one priority when we evaluate at IT infrastructure from a business standpoint. Databases run on instances within a VPC, so network is the first layer of defense. While creating the database, we choose whether database is publicly accessible or not. Even after making database publicly accessible, we can protect the database using restrictive security groups. It ensures that only the IP addresses and ports we are using are allowed, and no one else can access data. Here are the steps we need to follow to connect RDS DB instance from local SSMS and restrict others from accessing database.
Sign in to the AWS Management Console and open the RDS console. Choose Databases to display a list of DB instances.
Choose the SQL Server DB instance name to display its details.
On the Connectivity & security tab, click on the VPC security groups.
Security groups enable us to control traffic to DB instance, including the kind of traffic that can reach DB instance. Default inbound rule of security groups do not enable us to access DB instance from the internet. To enable network access to DB instance, one must allow inbound traffic.
In the security group console, select the security group associated with DB Instance and go to inbound rules. Click on edit inbound rules.
Now add a new inbound rule. Select type as MSSQL or All traffic, for Source choose My IP, this allows access to the DB instance from the IP address detected in the browser.
Click on save rules. The inbound rules will get updated in the security group as below.
By selecting anywhere for source, defaults IP’s 0.0.0.0/0 and ::/0 get added. Use of 0.0.0.0/0 is to enable all IPv4 addresses to access DB instance. Use of ::/0, to enable all IPv6 address to access DB instance. This is acceptable for a short time in a test environment, but it’s unsafe for production environments. In production, you authorize only a specific IP address or range of addresses to access your instance.
Go to the outbound rules tab. The outbound rules allow the traffic to leave the subnet. Add the new outbound rule. Select type as MSSQL or All traffic. Considering the security issues, choose My IP for Source.
Click on save rules. The outbound rules will get updated in the security group as below.
On the Connectivity & security tab, copy the endpoint. Also, note the port number. You need both the endpoint and the port number to connect to the DB instance.
Start SQL Server Management Studio.
The Connect to Server dialog box appears. Click on database engine.
Provide the information of your DB instance:
- For Server type, choose Database Engine.
- For Server name, enter the DNS name and port number of your DB instance, separated by a comma.·
- For Authentication, choose SQL Server Authentication.·
- Enter the user name and password of your DB instance.
- Choose Connect.
Now you can connect to your RDS DB instance and access your database without any issue.