Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enforce SSL for connecting to AWS RDS Instance of SQL Server


By:   |   Last Updated: 2019-03-25   |   Comments   |   Related Tips: More > Amazon AWS

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.

amazon relational database

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.

amazon rds

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.

AWS RDS

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.

AWS RDS

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

18) Navigate to the databases tab, click on the mssqlserver instance to open it, and click on the Modify button as shown below.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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.

AWS RDS - SQL Server

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


Last Updated: 2019-03-25


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools