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.
We enabled Public accessibility configuration to connect the database from the local machine outside the Amazon cloud.
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.
|
1 2 |
Select session_id,encrypt_option from sys.dm_exec_connections Where auth_scheme='SQL' |
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.
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.
- Force Secure Socket layer(SSL) for all connections
- 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.
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).
-
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.
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.
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
Click on Edit parameters and modify the value for rds.force_ssl to 1.
Click on save changes. You get an error message while saving the change.
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.
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.
|
1 |
SELECT @@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.
Click on Create. It creates a copy of the default parameter group with the specified name.
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.
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.
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.
Settings
It shows property for DB instance identifier, master password.
Network & Security
It gives an option for the Subnet group, VPC, Public accessibility.
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.
Backup, Monitoring and Maintenance configurations
We can modify backups, monitoring and DB maintenance configurations using this section.
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.
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.
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.
Click on Modify DB instance, and it returns to 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.
Once the status becomes available, execute the query to see SQL connection encryption status. It still shows false value for encryption.
An SSL change requires a reboot of AWS RDS SQL instance. Click on Actions followed by Reboot.
It asks you for the confirmation of reboot operation. Click on Reboot.
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.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023