Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup)

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.

File resources

• CloudFormation – Download template file.

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:

1. Create AWS resources using a CloudFormation template
2. Create an RDS Custom for SQL Server instance using AWS console
3. Create an EC2 instance with a Windows OS AMI
4. Use SQL Server Management Studio (SSMS) to connect to Amazon RDS Custom for SQL Server from the Windows EC2 instance
5. 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:

1. Download the json file to your computer
2. On the AWS CloudFormation console, choose Create stack
3. Select With new resources (standard)
4. Select Template is ready
5. For Template source, choose Upload a template file
6. For Choose file, locate and choose the file that you downloaded earlier
7. Choose Next
   
8. For Stack name, enter a name. In this example, we use the name rds-custom-sqlserver
9. 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.

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) ?” allows you to enable the RDP connection from the EC2 instance to the RDS Custom for SQL Server instance.

10. Choose Next
11. On the Configure stack options page, choose Next
12. On the Review page, for Capabilities, select I acknowledge that AWS CloudFormation might create IAM resources with custom names
13. Choose Create stack
    The stack creation process might take approximately 10 minutes.
14. 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.
    
15. On the Outputs tab, note the details

The following table summarizes our sample stack creation outputs.

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:

1. On the Amazon RDS console, in the navigation pane, choose Databases.
2. Choose Create database and select Standard create.
3. For Engine options, choose Microsoft SQL Server for the engine type.
4. For Database management type, choose Amazon RDS Custom.
5. In the Edition section, choose the DB engine edition that you want to use. In this example, we choose SQL Server Web Edition.
6. For Database version, choose your database version. For this post, we choose the default SQL Server 2019 engine available version.
   
7. In the Settings section, for DB instance identifier, enter a unique name. In this example, we use the name test-rds-custom-sqlserver.
8. In the Credential Settings section, enter the primary user name and password, and choose Confirm password.
9. In the Instance configuration section, choose a value for DB instance class.
10. 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.
11. In the Availability & durability section, specify whether to create a standby instance in a different availability zone to provide high availability. In this example, we specify not create a standby instance (single-az).
    

    Note: If you choose to create a standby instance (multi-az), make sure that the CloudFormation stack parameter Select an availability configuration for prerequisites setup was set to Multi-AZ. If the CloudFormation stack was created with Single-AZ, you can update the CloudFormation stack to Multi-AZ configuration

12. 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:
    1. VPC – rds-custom-sqlserver-vpc
    2. RDSCustomDBSubnetGroup – rds-custom-sqlserver-db-subnet-group
    3. Public access – No
    4. 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)
    5. Port – 1433
       
13. 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:
    1. RDSCustomIAMInstanceProfile – AWSRDSCustom-rds-custom-sqlserver-us-west-2
    2. RDSCustomKMSKey – rds-custom-sqlserver-kms-key
       
14. For RDS Custom Database Automation, select Full Automation.
15. 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.

1. On the Amazon EC2 console, choose Launch an instance.
2. Enter a name (for this post, we use ec2-windows-test-node).
3. 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.
4. For Instance type¸ choose your instance type (for this post, we use t2.small).
5. 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.
6. In the Network Settings section:
7. For Network Settings,
   1. Specify the VPC, subnet, and security group details.
   2. For Auto-assign public IP, choose Enable.
   3. 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

   

8. In the Configure storage section, specify the root storage size and volume type. For this example, we use GP2 with 50 GiB storage size
9. 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:

1. On the Amazon EC2 console, navigate to the EC2 instance you created (for this post, ec2-windows-test-node)
2. Select the instance and choose Connect
3. Choose Get password
4. Specify the key pair and choose the decrypted password
5. 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.
   
6. When you’re on the Windows EC2 instance, in the search window, search for SSMS, choose Connect, and Database Engine
7. 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
8. 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:

1. 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.
2. Select this instance and choose Connect
3. On the Session Manager tab, choose Connect
   
   A separate Session Manager window opens.
4. Run the following command to set up a firewall rule to allow the RDP connection:

   Set-NetFirewallRule -DisplayName "Remote Desktop - User Mode (TCP-In)" -Direction Inbound -LocalAddress Any -Profile Any

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:

1. From the Connect to Instance interface, choose the RDP client.
2. Download the remote desktop file.
3. Choose Get password.
   
4. Copy the key pair associated with this instance, key pair name starts with do-not-delete as shown in the following figure.
   
5. On the AWS Secrets Manager console, search for this key pair name.
6. Select the key and choose Retrieve secret value to display the content of the RSA private key.
   
7. Copy the entire plain text of the secret value, and paste this content into the Get Windows password section, as shown in earlier image.
8. 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:

1. Copy the remote desktop file to the EC2 instance (ec2-windows-test-node).
2. Open the remote desktop file.
3. 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.