Explore the prerequisites required to create an Amazon RDS Custom for SQL Server instance

Amazon Relational Database Service (Amazon RDS) Custom automates the setup, operation, monitoring, and scaling of relational databases in the cloud, while granting full access to the database and underlying operating system to configure settings, install drivers, and enable native features to meet the dependent application’s requirements.

Many organizations typically have different networking and database teams. Networking teams focus on designing, deploying, and managing the infrastructure and connectivity. They design the network to be highly secure, scalable, and reliable. Database teams focus on creating, configuring, and maintaining the database servers.

Customers often ask us how they can create an RDS Custom for SQL Server database in their existing networking infrastructure. They want to ensure that the database servers are created within the security perimeter designed by their networking teams. They also want to understand different components and services involved when creating an RDS Custom for SQL Server instance.

In this post, we demonstrate how to create an RDS Custom for SQL Server instance. We also show how to create the required prerequisites within an existing networking infrastructure. Amazon RDS Custom requires these prerequisites to create the necessary resources in your AWS account.

Solution overview

Before creating your first RDS Custom for SQL Server instance, you need to configure the following prerequisites:

• An AWS Identity and Access Management (IAM) user or role with required permissions
• A symmetric AWS Key Management Service (AWS KMS) key
• An IAM role and instance profile
• Endpoints for dependent AWS services

After you create these prerequisites, you can reuse these objects to create additional RDS Custom for SQL Server instances.

In this post, we show you how to configure the prerequisites and then perform the following actions:

1. Create an RDS Custom for SQL Server instance.
2. Validate the instance.

Prerequisites

The following sections describes the prerequisites in a greater detail. You can implement these steps either from the AWS Management Console or using the latest version of the AWS Command Line Interface (AWS CLI).

Create an IAM user or role with required permissions

IAM roles provide a way to access AWS resources. Each role has a set of permissions defined using policies that determine what it can and cannot do in AWS.

Before you proceed further, ensure you have the required permissions. The IAM user we create has the AdministrativeAccess policy attached to it. However, you may not need such elevated access. For example, to create a customer managed KMS key, you only need kms:CreateKey permissions.

To create the IAM user with required access, complete the following steps:

1. On the IAM console, choose Users in the navigation pane.
2. Choose Create user.
3. For User name, enter a name. For this post, we use awscliuser.
4. Select Provide user access to the AWS Management Console – optional if you want to configure prerequisites using the console.
5. Select I want to create an IAM user if you need to enable programmatic access through access keys, service-specific credentials for AWS CodeCommit or Amazon Keyspaces(for Apache Cassandra), or a backup credential for emergency account access.
6. For Console password, select Custom password and enter a strong password.
7. Choose Next.
   
   
8. For Permission options, select Attach policies directly.
9. Under Permissions policies, select the appropriate access policy. For our use case, we select AdministratorAccess.
10. Choose Next.
    
11. Review your choices and choose Create user.
12. In the Retrieve password section, choose Download .csv file.

This file contains user credentials and the console sign-in URL.

You have successfully created the IAM user with console access.

Grant AWS CLI access to the user

To grant AWS CLI access to the user you created, complete the following steps:

1. On the IAM console, choose Users in the navigation pane.
2. Choose the user name that you created (for this post, awsclisuer).
3. In the Summary section, for Access Key 1, choose Create access key.
   
4. For Access key best practices & alternatives, under Use case, choose Command Line Interface (CLI).
5. Under Confirmation, confirm that you understand the preceding recommendation.
6. Choose Next.
7. Choose Create access key.
8. For Retrieve access keys, choose Download .csv file.

You have successfully granted AWS CLI access to the awscliuser IAM user. You can now implement the prerequisites either from the console or using the latest version of the AWS CLI.

To verify access, use the following AWS CLI command to get the current user:

aws iam get-user

Use the following code to list all the managed policies that are attached to the specified IAM user:

aws iam list-attached-user-policies --user-name <User name>

Before you proceed further, ensure you have the required permissions.

Create a symmetric KMS key

A symmetric encryption KMS key is required for Amazon RDS Custom to encrypt and decrypt the data. Amazon RDS Custom supports only customer managed KMS keys. To check if you have an existing customer managed symmetric encryption KMS key in your account, complete the following steps:

1. On the AWS KMS console, choose Customer managed keys in the navigation pane.
2. Verify if the customer managed keys exist.
   
3. To create a new customer managed key, choose Create key.
4. For Key type, select Symmetric.
5. For Key usage, select Encrypt and decrypt.
6. Choose Next.
   
7. For Alias, enter an alias name for the KMS key. For this post, we enter rds-custom-key. The alias name cannot begin with aws/. The aws/ prefix is reserved by AWS to represent AWS managed keys in your account.
8. Choose Next.
   
9. Select the IAM users and roles that can administer the KMS key. For our use case, we select Admin. This key policy gives the AWS account full control of this KMS key. It allows account administrators to use IAM policies to give other principals permission to manage the KMS key. For details, see Default key policy.
10. Optionally, to prevent the selected IAM users and roles from deleting this KMS key, in the Key deletion section, deselect Allow key administrators to delete this key.
11. Choose Next.
    
12. Select the IAM users and roles that can use the key in cryptographic operations. For our use case, we select Admin.
13. Choose Next. As before, this key policy gives the AWS account full control of this KMS key, and allows account administrators to use IAM policies to give other principals permission to manage the KMS key.
14. Review the key settings that you chose. You can still go back and change all the settings.
15. Choose Finish to create the KMS key.

You can see the new KMS key on the Customer managed keys page.

You can also complete these steps using the AWS CLI.

To list all KMS keys in the account and Region using the AWS CLI, use the following command:

aws kms list-keys

aws kms describe-key --key-id <key-id>

aws kms create-key

Create an IAM role and instance profile

Access in AWS is managed by creating policies and attaching them to IAM roles or AWS resources. Permissions in the policies determine what access the role has on the resource. Amazon Elastic Compute Cloud (Amazon EC2) is one of the core resources of Amazon RDS Custom. This policy must allow Amazon EC2 to perform various actions like send and receive messages to AWS Systems Manager. It must also allow Amazon EC2 to communicate to other Amazon services such Amazon Simple Storage Service (Amazon S3), Amazon CloudWatch, and Amazon Simple Queue Service (Amazon SQS). For this, you need to create an IAM role and add a policy to it. When you attach a managed policy to the role, it is used as part of the role’s access (permissions) policy.

An instance profile is a container for an IAM role that you can use to pass role information to an EC2 instance when the instance starts. Therefore, you need to create an instance profile and add the IAM role to it. The following diagram shows the typical flow when creating the IAM role and instance profile.

The steps are as follows:

Note: RDS Custom provides an automated option to create and configure an instance profile when using AWS Console. If you are planning to use AWS Console to launch an RDS Custom instance, you can skip this section and use the automated instance profile creation feature. For more information see Automated instance profile creation from AWS Console: Setting up your environment for Amazon RDS Custom for SQL Server.

1. Create the IAM role.
2. Attach the permissions to the IAM role with IAM policies.
3. Create the instance profile (AWS CLI only) and add the IAM role to the instance profile.

The prefix AWSRDSCustom is required for the IAM instance profile and its associated IAM role names. To create an IAM role and inline policy, follow these steps:

1. On the IAM console, choose Roles in the navigation pane.
2. Choose Create role.
3. For Trusted entity type, select AWS service.
4. For Common use cases, select EC2.
   
5. Choose Next.
6. On the Add permissions page, choose Next.
7. For Role name, enter AWSRDSCustomSQLServerInstanceRole.
8. Enter a description for the role and choose Create role.

The following example creates the AWSRDSCustomSQLServerInstanceRole role using the AWS CLI. The trust policy lets Amazon EC2 assume the role.

aws iam create-role \
    --role-name AWSRDSCustomSQLServerInstanceRole \
    --assume-role-policy-document '{
        "Version": "2012-10-17",
          "Statement": [
            {
              "Action": "sts:AssumeRole",
              "Effect": "Allow",
              "Principal": {
                  "Service": "ec2.amazonaws.com"
              }
            }
          ]
        }'

Next, we add an access policy to AWSRDSCustomSQLServerInstanceRole.

9. From the list of roles, choose AWSRDSCustomSQLServerInstanceRole.
10. In the Permissions policies section, choose Add permissions and Create inline policy.
    
11. Choose AWS managed policy AmazonRDSCustomInstanceProfileRolePolicy from the list and click Add permissions.
    

Configure endpoints for dependent AWS services

Amazon RDS Custom sends communication from your DB instance to other AWS services within your VPC. To ensure all RDS Custom automation works as expected, it validates network connectivity to the following AWS services:

• Amazon CloudWatch
• Amazon CloudWatch Logs
• Amazon CloudWatch Events
• Amazon EC2
• Amazon EventBridge
• Amazon S3
• AWS Secrets Manager
• AWS Systems Manager
• Amazon SQS

We recommend that you add VPC endpoints for these services in your VPC. However, you can use any solution that lets your VPC communicate with AWS service endpoints. For example, you can use design patterns such as internet gateways, Network Address Translation (NAT) gateways with internet gateways, VPC peering, or transit gateways to direct your network to the required AWS service endpoints.

In this section, we explain how you can use VPC endpoints to connect to the AWS services in your VPC. The VPC endpoint enables resources in your VPC to privately connect to AWS services without leaving the Amazon network.

To configure VPC endpoints for AWS services that Amazon RDS Custom communicates with, complete the following steps using Amazon Virtual Private Cloud (Amazon VPC):

1. On the Amazon VPC console, choose your AWS Region.
2. Choose Endpoints in the navigation pane.
3. Choose Create endpoint.
   
4. For Service category, select AWS services.
5. For Service Name, enter an endpoint based on your service. For example, enter com.amazonaws.us-west-2.ssm.

We include a table with endpoint formats later in this section.

6. For VPC, choose your VPC.
7. For Subnets, choose a subnet from each Availability Zone to include.

The VPC endpoint can span multiple Availability Zones. AWS creates an elastic network interface for the VPC endpoint in each subnet that you choose.

8. For Security group, choose or create a security group.

Optionally, you can attach a policy to the VPC endpoint. Endpoint policies can control access to the AWS service to which you are connecting. The default policy allows all requests to pass through the endpoint. If you’re using a custom policy, make sure that requests from the DB instance are allowed in the policy.

Whether you choose an existing security group or create new, allow TCP port 443 inbound access from the RDS Custom DB instance.

For example, you can create a new security group for VPC endpoint named rds-custom-vpc-endpoint-sg. Next, allow TCP port 443 inbound access from the RDS Custom DB instance security group rds-custom-rds-custom-instance-sg. Refer to the following screenshot. You use the rds-custom-rds-custom-instance-sg security group when you create an RDS Custom DB instance.

9. Choose Create endpoint.
   
10. Repeat these steps for the remaining endpoints outlined in the following table, except for Amazon S3.
11. For Amazon S3, choose the service type as Gateway when creating a VPC endpoint.

The following table explains how to find the list of endpoints that your VPC needs for outbound communications.

Use the following commands in the AWS CLI to create VPC endpoints. Replace <vpc-id>, <subnet-ids>, <security-group-id>, and <route-table-id> with appropriate values from your networking infrastructure. <route-table-id> is a requirement for the Amazon S3 gateway endpoint only; it’s the route table associated with the subnet where you want to use the Amazon S3 VPC endpoint.

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.ssm \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.ssmmessages \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.secretsmanager \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.monitoring \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.events \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.logs \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.ec2 \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.ec2messages \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--service-name com.amazonaws.us-west-2.s3 \
--route-table-ids <route-table-id>

aws ec2 create-vpc-endpoint \
--vpc-id <vpc-id> \
--vpc-endpoint-type Interface \
--service-name com.amazonaws.us-west-2.sqs \
--subnet-ids <subnet-ids> \
--security-group-id <security-group-id>

To verify the endpoints, navigate to the Endpoints page on the Amazon VPC console and check the Status column. All the Endpoint type values should show as Interface, except for S3, which should show as Gateway.

Use the following AWS CLI command to verify the endpoints:

aws ec2 describe-vpc-endpoints

Now that you have created the prerequisite components, you can create one or many RDS Custom for SQL Server instances.

Create an RDS Custom for SQL Server instance

To create an RDS Custom for SQL Server instance, perform the following steps:

1. On the Amazon RDS console, choose Create database.
2. Select Microsoft SQL Server for Engine type.
3. For Database management type, select Amazon RDS Custom.
   
4. Choose the appropriate edition of SQL Server. For our use case, we choose SQL Server Standard Edition.
5. Choose the latest engine version on the drop-down menu.
6. Under Settings, provide the DB instance identifier, primary user name, and primary password.
   
7. Under Instance configuration, choose an appropriate DB instance class (Standard classes or Memory optimized classes).
8. Under Storage, choose the desired storage class from gp2, gp3, or io1 and enter an appropriate storage amount.
9. Under Availability & durability, choose the appropriate setting for Multi-AZ deployment.

We recommend enabling Multi-AZ deployment for production workloads.

10. In the Connectivity section, choose your VPC, DB subnet group, and VPC security group.

Whether you configure a new VPC security group or choose existing one, make sure that the security group has the following permissions:

• Allow TCP port 443 outbound access to the VPC endpoint security group
• Allow TCP port 443 outbound access to the Amazon S3 gateway endpoint
• For a Multi-AZ setup, ensure that TCP port 1433 and TCP port 1120 are allowed in both inbound and outbound rules from the RDS Custom for SQL Server security group
• Add a rule in your private network Access Control List (ACL) that allows TCP ports 0-65535 for the source subnet of the DB instance
  

11. Under RDS Custom Security, choose AWSRDSCustomSQLServerInstanceRole. You can choose from the following 2 options for IAM instance profile:
    • To create a new instance profile using AWS Console: choose Create a new instance profile and provide an instance profile name suffix. For more information, see Automated instance profile creation from AWS Console: Setting up your environment for Amazon RDS Custom for SQL Server
    • To use an existing instance profile: If you have manually created and configured instance profile as mentioned in Create an IAM role and instance profile step, then choose AWSRDSCustomSQLServerInstanceRole from the drop down list.
12. For Encryption, choose the symmetric KMS key you created earlier.
13. Choose Create database.

Use the following command to create an RDS Custom for SQL Server instance with the AWS CLI:

aws rds create-db-instance \
—db-instance-identifier rds-custom-db \
—engine custom-sqlserver-se \
—kms-key-id <key-id> \
—engine-version 15.00.4261.1.v1 \
—master-username admin \
—master-user-password ********* \
—db-instance-class db.m5.2xlarge \
—allocated-storage 200 \
—storage-type gp3 \
—region us-west-2 \
—custom-iam-instance-profile AWSRDSCustomSQLServerInstanceRole \
--vpc-security-group-ids <security-group-id> \
—db-subnet-group <db-subnet-group>

Validate the instance

To validate that the RDS Custom for SQL Server instance is created successfully, perform the following steps:

1. Remote Desktop to the EC2 host created as a prerequisite.
2. Open SQL Server Management Studio and connect to the RDS Custom for SQL Server instance endpoint.
3. Run the following command and take note of the SQL Server version and EC2 host name for the RDS Custom for SQL Server instance:

SELECT @@VERSION AS 'Version'

SELECT @@SERVERNAME AS 'Host Name'

Clean up

To avoid future charges and remove the components created while testing this use case, complete the following steps:

1. On the Amazon RDS console, choose Databases in the navigation pane.
2. Select the database you set up, and on the Actions menu, choose Delete.

Summary

In this post, we demonstrated how to set up the prerequisites to create an RDS Custom for SQL Server instance in your existing infrastructure. With this one-time configuration, you can reuse these objects to create RDS Custom for SQL Server instances within your networking infrastructure in the future.

If you have any comments or feedback, leave them in the comments section.

About the Author

Minesh Chande is Senior Database Specialist Solutions Architect at Amazon Web Services. He helps customers across different industry verticals design, migrate, and optimize their SQL Server workloads to a managed database platform like Amazon RDS and Amazon RDS Custom.