By: Siddharth Mehta | Updated: 2019-03-25 | Comments | Related: > Amazon AWS RDS
Problem
Security is a major concern when there is any incoming or outgoing traffic to database instances hosted on the cloud, from client tools and applications over an open internet route. Secure Socket Layer connections is one of the primary means of encrypting connections between the client and server, to reduce the possibility of any connection spoofing or network traffic tampering. Publishing it as a standard may not be enough for database administrators to enforce the use of SSL by all clients connecting to the database instance. The need is for a means to enforce all clients to use SSL for connecting to the database instance in the most transparent manner possible. In this tip we will learn how to achieve this for a SQL Server instance hosted on AWS Relational Database Service.
Solution
AWS RDS provides parameters in parameter groups which can be modified to enforce clients to use SSL connections on an RDS instance of SQL Server in a transparent manner. Upon creation of a database instance, RDS creates an SSL certificate.
In this tip, we are going to perform an exercise to learn how to enforce SSL connections to an AWS RDS instance of SQL Server. It is assumed that you have an AWS Account to try out this exercise. Please keep in mind that performing the below exercise will incur some cost.
1) Log in to your AWS account and open the Amazon RDS homepage as shown below. For beginners, you can search for RDS in the AWS Console and click on the RDS link to open the below homepage. In case if you already have an RDS database instances in your account and region, you should see the list of instances instead of the home page.
2) We intend to create an RDS SQL Server instance, where we can work with the technique of enforcing SSL connections for the clients. To start creating this instance, click on the Create Database button on the top-left. After clicking this button, you will be taken to the first step of database creation wizard as shown below.
3) You can use any edition of SQL Server, but for this demonstration I have selected Enterprise Edition as shown below. After selecting the appropriate edition, click the Next button.
4) In Step 2, we need to select the use-case for which we intend to set-up the SQL Server instance. This impacts the default values of the server parameters. Select Dev/Test for now and click on the Next button.
5) In Step 3, we need to specify the database instance related details. We will stick to the default options here. If you wish to change the engine version or size of the instance, or any other settings of the database instance, you can do that here. The settings do not impact the objective of our exercise.
6) Scroll down to the very bottom, and provide instance name, admin user name and password with which we will use to connect to the instance. After providing these details, click on the Next button.
7) In Step 4, we are going to leave all the settings as the defaults. Ensure that "Publicly Accessible" option is set to "Yes", so that we can connect to this instance from our local machine.
8) Scroll further down, and you should be able to see the details like port, parameter group and option group. Take note of the default parameter group that is being used. We are going to work with parameter groups once the database instance is created.
9) Scroll to the very bottom and click on the Create Database button, leaving all the rest of the settings as the defaults. If you wish you can change the network related settings, but this won't have any impact of the objective of this exercise.
10) Once you click on the button, you will get a confirmation message as shown below. Please wait for a couple of minutes after clicking on the Create database button, as AWS sets up a SQL Server RDS instance.
11) Click on the View DB Instance details button to check out the instance details. Once your instance is available, you should be able to view the instance configuration as shown below.
12) Click on the Connectivity & security tab, and you will be able to see the endpoint of the database instance. This is the url to use to connect to the AWS RDS instance of SQL Server, from any client that can connect to SQL Server. It is assumed that you will add your local machine IP to the security group of networks in which the RDS instance is created. You can refer to Step 7 of this tip to learn how to add your IP to the security group.
13) Now that the instance is ready to be accessed, use the endpoint and try to connect to it using SSMS. You need to provide the instance URL as the server name, select SQL Server Authentication, and provide the login id and password that you provided while creating the database instance. After providing these details, click on the Connect button.
14) Once you can successfully connect to the RDS instance, click on the New Query button and type the query as shown below. The dm_exec_connections is a system dynamic management view that returns server wide connection information. We are querying the dm_exec_connections view where authentication scheme of SQL Server authentication has been used and checking whether these connections are using encryption. You will find the values as shown below which indicates the connections are not using encryption.
15) Navigate to the parameter groups in the RDS dashboard, open the default SQL Server Enterprise Edition parameter group as shown below, and search for force_ssl parameter. You will find the option as shown below. The default value is disabled. To enable it, we need to create a new parameter group as we cannot change the default parameter group values. After the new parameter group gets created, we need to change the value of this parameter and associate it with our instance by modifying the instance settings.
16) Navigate back to the parameter groups option, click on the Create Parameter Group menu item, and provide details for the parameter group as shown below. The parameter group family should be sqlserver-ee, provide a name as well as description for the new parameter group and click on the Create button.
17) It's time to change the value of rds.force_ssl parameter from 0 to 1. Click on the new parameter group from the parameter groups tab, click on the modify button, find this parameter and change the value from 0 to 1, and then save the changes by clicking on the Save changes button as shown below.
18) Navigate to the databases tab, click on the mssqlserver instance to open it, and click on the Modify button as shown below.
19) A new page will open with all the database related settings. Navigate to the section where the parameter group is specified, and change from the default parameter group to the newly created custom parameter group as shown below, and then click on the Next button.
20) The summary page will display the summary of changes that are about to be applied. Click on Apply Immediately option as shown below and then click on the Modify DB Instance button, to apply the changes with immediate effect. In production, one would want to wait for a green zone window when the system can be taken down, as this change will require a reboot of the instance.
21) After the change is applied, navigate to the database instance on the RDS dashboard, and reboot the instance from the Action menu as shown below.
22) After the instance is rebooted, go back to SSMS, reconnect to the instance and execute the below query again from SSMS. You will see that the connections to the instance are now encrypted.
In this way, using the rds.force_ssl parameter, all clients connecting to the RSD instance of SQL Server on AWS can be forced to use SSL for encrypting the connection.
Next Steps
- Consider exploring all other interesting parameter from the parameter group to exploit the maximum potential from the SQL Server instance AWS RDS service.
- Check out these other tips
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-03-25
