Rajendra Gupta
Summary of modification

Enforce SSL connections in AWS RDS SQL Server

October 7, 2019 by

This article explores a method to enforce SSL for all connections in AWS RDS SQL Server.

Introduction

Each organization is concerned about data security. In a traditional data center, organizations host their entire infrastructure in a data center. Administrators control the firewall rules, security policies, and domain rules. In a cloud infrastructure, we access all resources over the internet. We do have a mechanism in AWS cloud to secure our infrastructure using custom VPC, security groups, public/private subnets.

Suppose you configured an AWS RDS SQL Server instance. This database instance hosts your critical data. The following screenshot shows myrdsinstance running SQL Server Express edition.

You can use article AWS RDS SQL Server – Launching a new database instance for creating a new RDS SQL instance.

AWS RDS SQL Server instance

We enabled Public accessibility configuration to connect the database from the local machine outside the Amazon cloud.

RDS database endpoint and public accessibility

Let’s connect to this instance in SSMS using the endpoint. We want to check that it uses an encrypted connection or not. Open a new query window and execute the following query.

This query calls dynamic management view sys.dm_exec_connections to check connections that are using SQL authentication and their encryption status. It returns false value for encryption that indicates connections are not encrypted.

SSL connection

We can use connection encryption between client and RDS instance to reduce the connection spoofing or unauthorized network tampering.AWS uses the following two mechanisms for database connection encryption.

  1. Force Secure Socket layer(SSL) for all connections
  2. Encrypt specific connections for encryption

In this article, we will focus on forcing SSL for all connections in the AWS RDS SQL Server.

Let’s explore encryption mechanism in detail.

Force Secure Socket layer (SSL) for all connections

In this approach, AWS uses Secure Socket Layer (SSL) for all connections. All connections are forced to use SSL encryption. By default, RDS SQL does not use any encryption.

We need to enable rds.force_ssl in the parameter group and reboot the instance to activate this.

We defined a parameter group while creating the RDS SQL Server instance. By default, RDS provides default parameters group, and it contains default settings for all parameters.

We can view the parameter group from the configuration page of instance.

Go to the RDS dashboard and Navigate to the databases. Click on the RDS SQL instance myrdsinstance. It launches the following page.

Instance property We can divide this page into two parts:

  • First part gives the RDS instance summary such as DB identifier, class, region, availability zone and current activity. It also contains options Modify (modify configurations) and Actions (start, stop, delete).

    RDS instance summary

  • In the second part, it divides the AWS RDS SQL Server instance configuration into the following categories.
    • Connectivity and Security
    • Monitoring
    • Log & events
    • Configuration
    • Maintenance & backups
    • Tags

Click on the configuration. It gives you instance configuration details such as options group, ARN, Multi-AZ, Parameter group and IAM DB authentication. configuration page

As per the screenshot, my instance uses default parameter group default.sqlserver-ex-14.0.

Click on this parameter group. It gives you all configurations, their value, source, type (static or dynamic), description.

default parameter group

It is a long list of parameters, and we can use a filter box to search for the required parameter. For example, let’s search for configuration options for keyword SSL. It filters the parameter as per the keyword.

In the following screenshot, we can note that the current value for rds.force_ssl is zero. It allows the following values:

  • Value 0: disable SSL ( default value)
  • Value 1: Enable force SSL for all connections

rds.force_ssl

Click on Edit parameters and modify the value for rds.force_ssl to 1.

Modify rds.force_ssl  to 1

Click on save changes. You get an error message while saving the change.

Error while saving changes

We get this error message because AWS RDS SQL Server does not allow changing default parameter group values. We can create a new parameter group and change the value of the required parameters. Further, we can modify an RDS instance to use the new parameter group.

In the left-hand menu, click on the Parameter groups. It shows both default and custom parameter groups.

Parameter groups

Create a new parameter group for AWS RDS SQL Server

To create a new parameter group, click on Create parameter group.

It requires the following inputs.

Parameter group family

RDS supports Amazon Aurora, MySQL, MSSQL, Oracle, MariaDB, and PostgreSQL. In this parameter, choose the DB family and its version. My RDS instance is running SQL Server express edition version 14.0.3049.1. You can verify it by executing the following query in SSMS.

SQL Version

Let’s choose parameter group family sqlsever-ex-14.0 from the drop-down.

Group Name

Specify a name for this new parameter group. You should give a friendly name for the parameter group.

Description

Add a description in this text box. It helps you to distinguish parameter groups from the description. You do not need parameters comparison if RDS contains multiple parameter groups.

create  new parameter group

Click on Create. It creates a copy of the default parameter group with the specified name.

New parameter group

We can make changes to this new parameter group. Click on new parameter group rdssql-ssl and change the value of rds.force_ssl to 1. It does not give any error message once we save the changes.

change the value of rds.force_ssl

We have not modified the RDS instance to use this new parameter group. RDS instance is still running with the default parameter group. Let’s change the parameter group for the RDS instance.

Modify AWS RDS SQL Server instance to use the new parameter group

Navigate to the Databases tab and click on the myrdsinstance. Click on the Modify.

Modify AWS RDS SQL Server instance to use the new parameter group

It opens a new page that contains the following specifications.

Instance specifications

It gives configuration for license model, DB engine version, DB instance class, storage type, allocated space.

Instance specifications

Settings

It shows property for DB instance identifier, master password.

Settings

Network & Security

It gives an option for the Subnet group, VPC, Public accessibility.

Network & Security

Database Options

We can modify the instance port, DB parameter group, option group. We will use this section to modify the parameter group for AWS RDS SQL Server.

Database Options

Backup, Monitoring and Maintenance configurations

We can modify backups, monitoring and DB maintenance configurations using this section.

Backup, Monitoring and Maintenance configurations

Let’s focus on the database options section. In the database options, we can change the parameter groups for AWS RDS SQL Server. Select the new parameter group from the drop-down.

Database options to modify parameter group

Click on Continue. In the next page, we can see the following things:

  • Summary of modifications: It shows the changes. For example, we can see a change in the DB parameter group from default.sqlserver-ex-14.0 to rdssql-ssl
  • Scheduling of modifications: Suppose we do not want to apply the changes immediately for a production database instance. We want to do it in the scheduled maintenance window. By default, RDS applies the changes in the next scheduled maintenance window. You can see selected option – Apply during the next scheduled maintenance window

It also shows the maintenance window for RDS instance.

Summary of modification

If we want to apply changes immediately, select the option – Apply immediately. It gives you a potential expected downtime warning. We should be careful in applying changes immediately as it might require downtime for RDS instance.

Apply changes immediately

Click on Modify DB instance, and it returns to RDS instance property page.

RDS instance property page

Navigate to databases, and in the status, you can see status Modifying. It is applying the DB parameter group changes to the RDS instance.

status Modifying

Once the status becomes available, execute the query to see SQL connection encryption status. It still shows false value for encryption.

Encryption status

An SSL change requires a reboot of AWS RDS SQL instance. Click on Actions followed by Reboot.

Actions and Reboot

It asks you for the confirmation of reboot operation. Click on Reboot.

Reboot RDS

RDS instance status changes to Rebooting.SQL Server does not allow any connection during rebooting. You should reboot the instance during the approved downtime window.

Rebooting status

Once the instance is available again, check the encryption status of DB connections. We can see the encryption connections ( encrypt_option=True) between RDS instance and client.

Encrypted connection

Conclusion

We explored that using the rds.force_ssl parameter, and we can force SSL connections for all client requests to AWS RDS SQL Server instance. Users do not have to change any connection properties. By default, RDS forces SSL connections for all connections.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
389 Views