Amazon Relational Database Service (Amazon RDS) Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database (DB) environment. Amazon RDS Custom for SQL Server automates setup, operation, and scaling of databases in the cloud, while granting access to the database and underlying operating system to configure settings, install drivers, and enable native features to meet the dependent application’s requirements.
In this post, we explain how to launch an RDS Custom for SQL Server instance using a predefined AWS CloudFormation template that creates the required network setup (Amazon Virtual Private Cloud (Amazon VPC), subnets, security groups, and so on), AWS Identity and Access Management (IAM) profile, AWS Key Management Service (AWS KMS) keys, and more. We also launch Amazon Elastic Compute Cloud (Amazon EC2) with a Windows operating system (OS) Amazon Machine Image (AMI) and then use that to connect an RDS Custom for SQL Server instance.
Prerequisites
Before we begin, we assume that you meet the following prerequisites:
Basic knowledge of CloudFormation templates
Understanding of environment setup for Amazon RDS Custom for SQL Server
For more details, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.
This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.
We strongly recommend that you set up this in a non-production instance and run the end-to-end validations before you implement this solution in a production environment.
Solution overview
At a high level, we perform the following steps:
Create AWS resources using a CloudFormation template
Create an RDS Custom for SQL Server instance using AWS console
Create an EC2 instance with a Windows OS AMI
Use SQL Server Management Studio (SSMS) to connect to Amazon RDS Custom for SQL Server from the Windows EC2 instance
Configure the Remote Desktop Protocol (RDP) connection to the RDS Custom for SQL Server instance from the Windows EC2 instance
For this post, we use the us-west-2 AWS Region for all setup steps.
Create AWS resources using a CloudFormation template
The following steps explain how to create AWS resources using a CloudFormation template:
Save the file json to your computer
On the AWS CloudFormation console, choose Create stack
Select With new resources (standard)
Select Template is ready
For Template source, choose Upload a template file
For Choose file, locate and choose the file that you downloaded earlier
Choose Next
For Stack name, enter a name. In this example, we use the name rds-custom-sqlserver
In the Parameters section, either keep the default parameter values or specify the appropriate values as needed
The following table summarizes the parameters for our stack creation.
Parameter Name
Value
Description
IPv4 CIDR block for VPC
10.0.0.0/16
Specify an IPv4 CIDR block (or IP address range) for your VPC. The CIDR block size must have a size between /16 and /28.
IPv4 CIDR block for 1 of 2 private subnets
10.0.1.0/24
Specify an IPv4 CIDR block (or IP address range) for your first private subnet. The CIDR block size must be within the IP range of the VPC and a size between /16 and /28.
IPv4 CIDR block for 2 of 2 private subnets
10.0.2.0/24
Specify an IPv4 CIDR block (or IP address range) for your second private subnet. The CIDR block size must be within the IP range of the VPC, not overlapping with first subnet, and a size between /16 and /28.
IPv4 CIDR block for public subnet
10.0.3.0/24
Specify an IPv4 CIDR block (or IP address range) for your public subnet. The CIDR block size must be within the IP range of the VPC, not overlapping with both private subnets, and a size between /16 and /28.
IPv4 CIDR block of your source
101.102.103.104/32
Specify an IPv4 CIDR block (or IP address range) of your source. This is the IP range from where you make RDP connection to the EC2 instance. If left blank, the RDP connection to the EC2 instance isn’t configured.
Setup RDP access to RDS Custom for SQL Server instance (private subnets)?
Yes
Specify whether to open RDP access to private subnets from within the VPC, this enables you to perform RDP connection from EC2 instance to the RDS Custom for SQL Server instance.
The parameter IPV4 CDR Block of your source is the public IP address of the instance (where you’re going to connect (RDP) from your EC2 Windows instance). For this post, we’re using Windows desktop; it has the public IP address 101.102.103.104, and we make an RDP connection to the Windows EC2 instance from this Windows desktop. If you don’t specify this value, the RDP connection to the EC2 instance isn’t configured, but you can set this up manually at a later point.
The parameter Setup RDP access to RDS Custom SQL Server Instance (private subnets) ? let’s you enable the RDP connection from the EC2 instance to the RDS Custom for SQL Server instance.
Choose Next
On the Configure stack options page, choose Next
On the Review page, for Capabilities, select I acknowledge that AWS CloudFormation might create IAM resources with custom names
Choose Create stack
The stack creation process might take approximately 10 minutes.
When the stack creation is complete, navigate to the stack (rds-custom-sqlserver) and choose the Resources tab to review all the resources that were created as part of this CloudFormation template.
On the Outputs tab, note the details
The following table summarizes our sample stack creation outputs.
Key
Value
Description
EC2InstancePublicSubnet
subnet-0c0ddab3bf65e6abc (Name: rds-custom-sqlserver-public-subnet-1)
Public subnet for creating an EC2 instance to connect to the RDS Custom instance.
EC2InstanceSecurityGroup
sg-07fdfc15b738baabc (Name: rds-custom-sqlserver-ec2-instance-sg)
Security group that we attach to the EC2 instance, used to connect with the RDS Custom instance.
RDSCustomDBSubnetGroup
rds-custom-sqlserver-db-subnet-group
DB subnet group specified while creating an RDS Custom instance by using the parameter –db-subnet-group-name.
RDSCustomIAMInstanceProfile
AWSRDSCustom-rds-custom-sqlserver-us-west-2
Instance IAM profile specified while creating an RDS Custom instance by using the parameter –custom-iam-instance-profile.
RDSCustomKMSKey
aaff2ae5-a911-4605-a9ce-c33d48f8dabc (Alias: rds-custom-sqlserver-kms-key)
KMS key to encrypt data managed by RDS Custom instances, specified while creating an RDS Custom instance by using the parameter –kms-key-id.
RDSCustomSecurityGroup
sg-0928fe29155aecabc (Name: rds-custom-sqlserver-rds-custom-instance-sg)
Security group to be attached to the RDS Custom instance while creating an RDS Custom instance by using the parameter –vpc-security-group-ids.
VPC
vpc-0339dc0aafc172abc (Name: rds-custom-sqlserver-vpc)
VPC for which the DB subnet group is created.
Create your RDS Custom for SQL Server instance
When the CloudFormation stack creation is complete, we launch an RDS Custom for SQL Server instance. This can be done using AWS CLI or AWS Console.
To create your RDS Custom for SQL Server DB instance using AWS console, complete the following steps:
On the Amazon RDS console, in the navigation pane, choose Databases.
Choose Create database and select Standard create.
For Engine options, choose Microsoft SQL Server for the engine type.
For Database management type, choose Amazon RDS Custom.
In the Edition section, choose the DB engine edition that you want to use. In this example, we choose SQL Server Web Edition.
For Database version, choose your database version. For this post, we choose the default SQL Server 2019 engine available version.
In the Settings section, for DB instance identifier, enter a unique name. In this example, we use the name test-rds-custom-sqlserver.
In the Credential Settings section, enter the primary user name and password, and choose Confirm password.
In the Instance configuration section, choose a value for DB instance class.
In the Storage section, specify your values for Storage Type, Allocated Storage, and Provisioned IOPS. In this example, we specify Storage type as io1, Allocated storage as 100, and Provisioned IOPS as 3000.
In the Connectivity section, specify the VPC, subnet group, and security groups details, using the output values from our stack creation. For this post, we enter the following:
VPC – rds-custom-sqlserver-vpc
RDSCustomDBSubnetGroup – rds-custom-sqlserver-db-subnet-group
Public access – No
RDSCustomSecurityGroup – rds-custom-sqlserver-rds-custom-instance-sg (the default security group is auto selected; we remove that and add the actual security group (RDSCustomSecurityGroup) that was created as part of stack creation)
Port – 1433
In the RDS Custom security section, specify the IAM instance profile and KMS key, using the output values from our stack creation. For this post, we enter the following:
RDSCustomIAMInstanceProfile – AWSRDSCustom-rds-custom-sqlserver-us-west-2
RDSCustomKMSKey – rds-custom-sqlserver-kms-key
For RDS Custom Database Automation, select Full Automation.
Choose Create database.
Create an Amazon EC2 AMI with Windows
To launch an EC2 instance with a Windows OS AMI, complete the following steps. For this post, we use the new Amazon EC2 console.
On the Amazon EC2 console, choose Launch an instance.
Enter a name (for this post, we use ec2-windows-test-node).
For Application and OS Image (AMI) section, search for your Windows AMI. For this example, we choose Microsoft SQL Server 2019 Express on Windows Server 2019.
You can choose any Windows OS AMI that is compatible and install the SSMS tool on top of it.
For Instance type¸ choose your instance type (for this post, we use t2.small).
For Key pair name¸ you can choose the key pair you created or create a new one. For this post, we use the existing key pair rds-custom-keys.
In the Network Settings section:
For Network Settings,
Specify the VPC, subnet, and security group details.
For Auto-assign public IP, choose Enable.
Enter the output values from our stack creation. For this post, we enter the following:
VPC – rds-custom-sqlserver-vpc
EC2InstancePublicSubnet – rds-custom-sqlserver-public-subnet-1
EC2InstanceSecurityGroup – rds-custom-sqlserver-ec2-instance-sg
In the Configure storage section, specify the root storage size and volume type. For this example, we use GP2 with 50 GiB storage size
Choose Launch instance
Use SSMS to connect to Amazon RDS Custom for SQL Server from a Windows EC2 instance
After we create the RDS Custom for SQL Server instance and Windows EC2 instance, we make the RDP connection to the Windows EC2 instance, and from there we connect to the RDS Custom for SQL Server instance using SSMS.
If you didn’t specify the parameter IPv4 CIDR block of your source as part of the initial stack creation, the RDP connection to the EC2 instance isn’t configured. If required, you can perform those steps manually.
Complete the following steps to configure an SSMS connection to Amazon RDS Custom for SQL Server from a Windows EC2 instance:
On the Amazon EC2 console, navigate to the EC2 instance you created (for this post, ec2-windows-test-node)
Select the instance and choose Connect
Choose Get password
Specify the key pair and choose the decrypted password
On the RDS client tab, choose Download remote desktop file
In this example, we make an RDP connection from a Windows desktop host (101.102.103.104) by using the downloaded remote desktop file and decrypted password.
When you’re on the Windows EC2 instance, in the search window, search for SSMS, choose Connect, and Database Engine
For Server name, enter the RDS Custom for SQL Server endpoint, login, and password details that were specified when you created the RDS Custom for SQL Server instance
Choose Connect
Configure the RDP connection to the RDS Custom for SQL Server instance from a Windows EC2 instance
We now complete the steps to configure the RDP connection to Amazon RDS Custom for SQL Server from a Windows EC2 instance.
Set the firewall rules for the underlying EC2 instance of Amazon RDS Custom for SQL Server
Complete the following steps to set up Windows firewall to allow the RDP connection:
On the Amazon EC2 console, search for the underlying EC2 instance of the RDS Custom for SQL Server instance
This EC2 instance is created with the naming standard do-not-delete-<rds-custom-sqlserver-instance-name>. In our example, the instance name is do-not-delete-test-rds-custom-sqlserver.
Select this instance and choose Connect
On the Session Manager tab, choose Connect
A separate Session Manager window opens.
Run the following command to set up a firewall rule to allow the RDP connection:
Retrieve the secret key and download the remote desktop file
To create an RDP connection, we need the remote desktop file and Windows login password.
To get the Windows password, we have to provide the key pair. Complete the following steps to get the key pair details to decrypt the password:
From the Connect to Instance interface, choose the RDP client.
Download the remote desktop file.
Choose Get password.
Copy the key pair associated with this instance, key pair name starts with do-not-delete as shown in the following figure.
On the AWS Secrets Manager console, search for this key pair name.
Select the key and choose Retrieve secret value to display the content of the RSA private key.
Copy the entire plain text of the secret value, and paste this content into the Get Windows password section, as shown in earlier image.
Choose Decrypt password.
We’re now able to see the Windows login and password and save those details for completing the RDP connection.
Complete the RDP connection to the RDS Custom for SQL Server instance
Now that we have downloaded the remote desktop file and retrieved the Windows password, complete the following steps to complete the RDP connection to the RDS Custom for SQL Server instance:
Copy the remote desktop file to the EC2 instance (ec2-windows-test-node).
Open the remote desktop file.
Enter the password that you retrieved and saved earlier.
After you complete the RDP connection, you can access the OS file system as shown in the following screenshot and perform standard activities on the instance.
Clean up resources
If you no longer require this setup and want to avoid future charges, you can delete the resources that you created as part of this setup (namely, the RDS Custom for SQL Server and Windows EC2 instances). To delete all other resources that were launched as part of the CloudFormation stack, go to the AWS CloudFormation console, select the stack, and choose Delete.
Summary
In this post, we explained how to launch Amazon RDS Custom for SQL Server using a CloudFormation template and EC2 instance with a Windows AMI. We performed an RDP connection to the EC2 Windows instance, and from there we connected to Amazon RDS Custom for SQL Server using SSMS. Additionally, we made an RDP connection to the RDS Custom for SQL Server instance from the EC2 Windows instance and check the OS file system.
Try out the solution and if you have any comments or questions, leave them in the comments section.
About the authors
Srikanth Katakam is a Senior Database Engineer at Amazon Web Services. He works on the RDS team, focusing on commercial database engines, RDS Custom and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning from and sharing knowledge with his teammates and AWS customers.
Anand Sankar Bhagavandas is a Senior Software Development Engineer at Amazon Web Services. He works on the design and development of key features in RDS and RDS Custom for SQL Server.
Read MoreAWS Database Blog
