Accessing an Amazon RDS instance remotely using AWS Client VPN

November 2022: This post was reviewed and updated for accuracy.

Developers and database administrators, often login remotely to an Amazon Elastic Compute Cloud (Amazon EC2) instance on a public subnet and access the Amazon Relational Database Service (Amazon RDS) instance. For increased productivity and ease of use, in many cases, there is a need to login and access the RDS instance remotely from your favorite tools in your workstation without having to first login to the remote EC2 instance.

When creating an RDS instance, you have the option to make it publicly accessible to enable remote connectivity which is not advisable. In this post, we walk through the process of creating an RDS instance without making it publicly accessible and connecting to it remotely using AWS Client VPN. We are specifically using the example of Microsoft SQL Server in this blog post.

AWS Client VPN is a fully managed elastic VPN service that provides the ability to securely access AWS and on-premises resources from any location, using a VPN software client. It’s a highly available, elastic, and pay-as-you-go service.

Overview

The following diagram, shows the high-level architecture of an example scenario of using AWS Client VPN and connecting to an RDS instance.

The client establishes a VPN connection from their local computer using an OpenVPN based VPN client application. All client VPN sessions end at the AWS Client VPN endpoint, which is configured to manage all client VPN sessions.

A DB subnet group is a collection of subnets that are created in a VPC and designated for the DB instance. Each DB subnet group should have subnets in at least two Availability Zones in a given AWS Region. When creating a DB instance in a VPC, you must choose a DB subnet group.

After the connection is established, you can securely connect to the RDS instance in the subnet, which is associated to the AWS Client VPN endpoint. The RDS instance supports both SQL and Windows authentication using AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD).

In this use case, we create the AWS Client VPN to use mutual authentication. With mutual authentication, AWS Client VPN uses certificates to perform authentication between client and server.

This walkthrough shows you how to do the following steps:

1. Generate a server certificate and upload it to AWS Certificate Manager (ACM).
2. Create a VPC and subnets.
3. Create a security group and set up ingress rules.
4. Create a client VPN endpoint.
5. Create an Active Directory.
6. Create an RDS Instance.
7. Download and install VPN client software.
8. Export and configure the VPN client configuration file.
9. Connect the client VPN.
10. Verify connectivity to the RDS instance.

Prerequisites

This walkthrough requires the following:

• An AWS account
• The AWS Command Line Interface (AWS CLI) installed and configured on your machine

Kindly note that AWS commands in this article were tested with AWS CLI version 2.

Generating a certificate

For instructions on creating a server certificate using OpenVPN easy-rsa tool, see Mutual authentication.

Upload the server certificate into ACM using the following command (replace the file names with your own):

aws acm import-certificate \
     --certificate fileb://Certificate.pem \
     --certificate-chain fileb://CertificateChain.pem \
     --private-key fileb://PrivateKey.pem

After it’s uploaded, it generates a certificate ARN, which you use in a subsequent step.

Creating a VPC and subnets

Create a VPC to host the subnets and the subnet group for the RDS instance with the following code:

aws ec2 create-vpc \
--cidr-block "172.32.0.0/16" \
--tag-specifications 
"{\"ResourceType\":\"vpc\",\"Tags\":[{\"Key\":\"Name\",\"Value\":\"myrdsvpn\"}]}]"

You use the VPC ID to create two subnets in two different Availability Zones:

aws ec2 create-subnet \
--vpc-id "vpc-xxxxxxxxxxxxxxxxx" \
--cidr-block "172.32.1.0/24" \
--availability-zone "us-east-1a"

aws ec2 create-subnet \
--vpc-id "vpc-xxxxxxxxxxxxxxxxx" \
--cidr-block "172.32.2.0/24" \
--availability-zone "us-east-1b"

You use the subnet IDs in subsequent steps.

Creating a security group

Create a security group to be used by the AWS Client VPN endpoint and the RDS instance with the following code:

aws ec2 create-security-group \
--group-name "mysecuritygroup" \
--description "My security group" \
--vpc-id "vpc-xxxxxxxxxxxxxxxxx"

You also create two ingress rules attached to the security group.

The first rule allows connections from client IP CIDR to UDP port 443 for users to connect to the AWS Client VPN endpoint. The source IP is the IP address of the users connecting to the AWS Client VPN endpoint. See the following code:

aws ec2 authorize-security-group-ingress \
--group-id "sg-xxxxxxxxxxxxxxxxx" \
--protocol "udp" \
--port "443" \
--cidr "122.183.152.0/24"

The second rule allows TCP connections between all network interfaces attached to the security group, such as connections from the security group to itself:

aws ec2 authorize-security-group-ingress \
--group-id "sg-xxxxxxxxxxxxxxxxx" \
--protocol -1 \
--source-group "sg-xxxxxxxxxxxxxxxxx"

Creating an AWS Client VPN endpoint

Create an AWS Client VPN endpoint and attach it to the VPC with the following code. You use the client IP4 CIDR to assign IP addresses to the client connections. Use your own server certificate ARN generated in the previous step.

aws ec2 create-client-vpn-endpoint \
--client-cidr-block "192.168.128.0/22" \
--server-certificate-arn "arn:aws:acm:xxxxxxxxxxxxxxxxxxxxxxxx" \
--authentication-options "Type=certificate-authentication,
MutualAuthentication={ClientRootCertificateChainArn=arn:aws:acm:xxxxxxxxxxxxxxxxxxxxxxxx}" \
--vpc-id "vpc-xxxxxxxxxxxxxxxxx" \
--connection-log-options "{\"Enabled\": false}" \
--split-tunnel \
--tag-specifications "[{\"ResourceType\":\"client-vpn-endpoint\",\"Tags\":[{\"Key\":\"Name\",\"Value\":\"myclientvpnendpt\"}
]}]" \
--security-group-ids "sg-xxxxxxxxxxxxxxxxx"

The endpoint uses the split-tunnel option. You can use a split-tunnel AWS Client VPN endpoint when you don’t want all user traffic to route through the AWS Client VPN endpoint.

The AWS Client VPN endpoint is created with the status of pending associate. We must associate target networks to the endpoint. The following code associates the two subnets created earlier to the newly created AWS Client VPN endpoint:

aws ec2 associate-client-vpn-target-network \
--client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
--subnet-id "subnet-xxxxxxxxxxxxxxxxx"

aws ec2 associate-client-vpn-target-network \
--client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
--subnet-id "subnet-yyyyyyyyyyyyyyyyy"

After you run these commands, the status of the VPN endpoint changes to Associating and then to Associated, when it’s complete.

To authorize clients to access the VPC in which the associated subnet is located, you must create an authorization rule. The authorization rule specifies which clients have access to the VPC. In this walkthrough, we grant access to all users. The target network is the CIDR of the network that should be allowed access to the endpoint. In this case, ingress access is being allowed to the entire VPC. See the following code:

aws ec2 authorize-client-vpn-ingress \
--client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
--target-network-cidr "172.32.0.0/16" \
--authorize-all-groups

Creating an Active directory

Because the SQL Server RDS instance also uses Windows authentication, create an Active Directory to be associated to the RDS instance:

aws ds create-microsoft-ad \
--name "corp.mydirectory.com" \
--edition "Standard" \
--password "xxxxxxxx" \
--description "My Corp Microsoft AD" \
--short-name "mycorpdirectory" \
--vpc-settings "{\"VpcId\":\"vpc-xxxxxxxxxxxxxxxx\",\"SubnetIds\":[\"subnet-xxxxxxxxxxxxxxxxx\",\"subnet-yyyyyyyyyyyyyyyyy\"]}"

Creating the SQL Server RDS instance

To create an RDS instance, you need to create a subnet group and a directory service AWS Identity and Access Management (IAM) role. This IAM role uses the managed IAM policy AmazonRDSDirectoryServiceAccess and allows Amazon RDS to make calls to the active directory.

Create the IAM role with the following code:

aws iam create-role \
--role-name "rds-directory-service-access-role" \
--assume-role-policy-document "{\"Version\": \"2012-10-17\", 
\"Statement\": { \"Effect\": \"Allow\", \"Principal\": {\"Service\":
 \"rds.amazonaws.com\"}, \"Action\": \"sts:AssumeRole\"}}"

Attach the policy to the role with the following code:

aws iam attach-role-policy \
--role-name rds-directory-service-access-role \
--policy-arn arn:aws:iam::aws:policy/service-role/AmazonRDSDirectoryServiceAccess

A DB subnet group is a collection of subnets (typically private) that you create in a VPC and designate for your DB instances. Create the subnet group using the two subnets created earlier in the VPC with the following code:

aws rds create-db-subnet-group \
--db-subnet-group-name "mydbsubnetgroup" \
--db-subnet-group-description "My RDS Subnet Group" \
--subnet-ids "[\"subnet-xxxxxxxxxxxxxxxxx\",\"subnet-yyyyyyyyyyyyyyyyy\"]"

Next, create a SQL Server RDS instance associated to the subnet group and the VPC that was created earlier. Use the security group, Active Directory domain, IAM role and DB subnet group created earlier:

aws rds create-db-instance \
--db-instance-identifier "mysqlserver" \
--db-instance-class "db.t3.small" \
--engine "sqlserver-ex" \
--engine-version "15.00.4043.16.v1" \
--master-username "admin" \
--master-user-password "xxxxxxxx" \
--vpc-security-group-ids "[\"sg-xxxxxxxxxxxxxxxxx\"]" \
--no-publicly-accessible \
--domain "d-xxxxxxxx" \
--license-model "license-included" \
--allocated-storage 20 \
--domain-iam-role-name "rds-directory-service-access-role" \
--db-subnet-group-name "mydbsubnetgroup"

Downloading and installing AWS Client VPN

Download and install the latest software for AWS Client VPN.

Exporting and configuring the VPN client configuration file

Download the client configuration file using the following command:

aws ec2 export-client-vpn-client-configuration \
--client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
--output text > myclientconfig.ovpn

Because our AWS Client VPN endpoint uses mutual authentication, you must add the client certificate and the client private key to the configuration file that you download. To do this, open the configuration file using a text editor and add the following lines to the end of the file, providing the path to the client certificate and key that was created earlier.

cert /path/client1.domain.tld.crt
key /path/client1.domain.tld.key

Connecting to AWS Client VPN

To connect to AWS Client VPN, complete the following steps:

1. Open AWS Client VPN application.
2. On the File menu, choose Manage Profiles.
3. Choose Add Profile.
4. Add a display name and choose the VPN configuration file that was downloaded and modified.
5. Choose Add Profile.
6. Choose Done.
7. Choose the profile you created.
8. Choose Connect.

Verification

This step verifies connectivity to the RDS instance.

1. On the Amazon RDS console, on the navigation pane, choose Databases.
2. Choose the database instance you created (mysqlserver).
3. Copy the endpoint.
4. In the SQL Server Management Studio, for Server name enter the endpoint.
5. Enter a login and password.
6. Choose Connect.

Alternatively we can also connect to the RDS instance using windows authentication. When the AWS Managed Microsoft AD is created, it creates a Windows user Admin in the mycorpdirectory domain. It’s part of the BUILTIN domain user group and added to the SQL Server RDS instance.

1. Open a command prompt in elevated mode and enter the following code(provide the path to the folder that has SSMS.exe file):

   runas /netonly /user:corp.mydirectory.com\Admin "C:\[path to SSMS]\Ssms.exe"

2. Enter the AD Admin user password, which was provided during AD creation.

The SQL Server Management Studio opens.

3. For Authentication, choose Windows Authentication
4. Choose Connect.

You’re connected to the SQL Server RDS instance using the Windows login corp.mydirectory.com\Admin.

Cleaning up

To avoid incurring future charges, delete all resources created. Replace the resource identifiers in the following commands with the ID of the resources you created.

1. Delete the the associated target networks from the AWS Client VPN endpoint:

   aws ec2 disassociate-client-vpn-target-network \
   --client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
   --association-id "cvpn-assoc-xxxxxxxxxxxxxxxxx"
   
   aws ec2 disassociate-client-vpn-target-network \
   --client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx" \
   --association-id "cvpn-assoc-yyyyyyyyyyyyyyyyy"

2. Delete the AWS Client VPN endpoint with the following code:

   aws ec2 delete-client-vpn-endpoint \
   --client-vpn-endpoint-id "cvpn-endpoint-xxxxxxxxxxxxxxxxx"

3. Delete the RDS instance with the following code:

   aws rds delete-db-instance \
   --db-instance-identifier "mysqlserver"
   --skip-final-snapshot

4. Delete the Active Directory with the following code:

   aws ds delete-directory \
   --directory-id "d-xxxxxxxxxx"

5. Last, in order to delete a VPC using the command line, you must terminate or delete any resources that created a requester-managed network interface in the VPC, plus you must delete or detach all associated resources, such as subnets, custom security groups, custom network ACLs, custom route tables, internet gateways, and egress-only internet gateways. Finally, delete the VPC with the following code :

   aws ec2 delete-vpc \--vpc-id "vpc-xxxxxxxxxxxxxxxxx"

Summary

In this post, we demonstrated how you can connect to an RDS instance remotely without making it public using AWS Client VPN. You created a VPC, two subnets, an Active Directory, an RDS instance linked to the directory, an AWS Client VPN endpoint and an associated security group and IAM role. Then you connected using the AWS OpenVPN client software, and accessed the RDS instance.

AWS Client VPN can provide a useful, cost effective connectivity solution, especially for use cases that necessitate your workforce to be remote. You can access your RDS instance in a private subnet using AWS Client VPN, which can be quickly scaled and easily deployed to provide secure access to your resources on AWS.

About the Authors

Gopalakrishnan Ramaswamy is a Solutions Architect at AWS based out of India with extensive background in database, analytics, and machine learning. He helps customers of all sizes solve complex challenges by providing solutions using AWS products and services. Outside of work, he likes the outdoors, sports activities and spending time with friends and family.

Nishant Dhiman is a Solutions Architect at AWS with an extensive background in Serverless, Security and Mobile platform offerings. He is a voracious reader and a passionate technologist. He loves to interact with customers and always relishes giving talks or presenting on public forums. Outside of work, he likes to keep himself engaged with podcasts, calligraphy and music.