Secure your data with Amazon RDS for SQL Server: A guide to best practices and fortification

Securing SQL Server databases in the cloud is critical, and Amazon Relational Database Service for SQL Server (Amazon RDS) provides several security features to help ensure the confidentiality, integrity, and availability of your database instances. These features include data encryption at rest and in transit, secure user authentication and authorization mechanisms, network isolation, and fine-grained access control.

In this post, we show you best practices to increase the security posture of your Amazon RDS for SQL Server instances.

Cloud security overview

The three main elements of cloud security are authentication, authorization, and auditing, which can be further classified into the processes shown in the following diagram.

The processes are as follows:

Network security – Network security involves protecting the underlying infrastructure from unauthorized access and threats and securing the infrastructure to only allow authorized access. Amazon Virtual Private Cloud (Amazon VPC) provides a logically isolated section of the AWS Cloud where you can launch AWS resources in a virtual network of your choice. It allows you to control the security and network configuration of your AWS infrastructure, including network access, network segregation, and network traffic flow.
DB authentication – This is the process of authenticating a user or system seeking to access a database. This may be accomplished using a variety of ways, including login and password authentication, multi-factor authentication, and certificate-based authentication, in order to ensure safe access to critical data.
DB authorization – This is the process of granting access rights to a user or system once their identity has been authenticated. This can involve providing rights to do certain tasks, such as reading or writing data, or access to specific database objects. This helps guarantee that data is safe and only authorized individuals and systems have access to it.
DB access audit – This process also referred to as accounting, ensures monitoring and recording of database activity in order to detect security problems, such as unauthorized access attempts, and to fulfill regulatory and compliance obligations. This may be accomplished by monitoring database events, configuring warnings, and analyzing logs to detect suspicious behavior, therefore giving an auditable trail of database access and modifications.
DB encryption – This is the process of preventing unwanted access to sensitive data contained in databases by encrypting the data both in transit and at rest. To help safeguard sensitive data in databases, AWS offers a variety of encryption solutions, including server-side encryption, client-side encryption, and snapshot encryption. This ensures that, even if illegal access is gained to the database, the encrypted data remains secure and unreadable.
DB security monitoring – This involves monitoring the security of databases and the systems and networks they operate on in order to discover and respond to security problems in a timely way. This may be accomplished by monitoring database events, configuring security alarms, and employing security analytics tools to detect and respond to security risks.

In the following sections, we discuss each component in more detail.

Network security

Network security is a critical step in protecting the database instance from unauthorized access. It’s important to ensure that all network traffic to the database originates from known sources that are explicitly allowed in the network configuration. This can be configured primarily using security groups and network access control lists (ACLs).

Security groups

Each RDS for SQL Server instance is associated with one or more security groups. These security groups allow you to control the traffic to your database instances by specifying an IP or a range of IP addresses as well as the port or port ranges through which to allow traffic to the instance.

To ensure that the security groups are put to best use, ensure that you add ingress rules for specific sources and the port number or ranges that need access to the database. Security groups are stateful in nature and allow outgoing responses automatically for incoming traffic that is allowed through the ingress rules.

Network access control lists

Network ACLs allow fine-grained control over your network traffic within a VPC. The network ACL rules are stateless, and you need to specify individual ingress and egress rules. These are particularly helpful for Amazon RDS when you need additional policies in place to ensure database isolation within a VPC.

You can create your Amazon RDS for SQL Server instance in a private subnet within your VPC and ensure that only specific traffic from certain subnets within the VPC can reach the Amazon RDS for SQL Server VPC by creating a network ACL and associating it with the Amazon RDS for SQL Server subnet. It is critical to remember that unlike security groups, NACLs are stateless and you would have to define the rules for both incoming and outgoing traffic. If you are using NACLs, make sure to allow traffic on the port 3343 for UDP and TCP, as this is required for Amazon RDS for SQL Server multi AZ instance.

It is important to note, that while NACLs enables you to control the flow of traffic at a subnet level, they could be complex to manage and configure. For that reason, if you need to allow traffic from defined sources and do not need to manage it at a subnet level, security groups would be a better option to define these rules as they operate at an instance level while the NACLs operate at the subnet level.

Remote connections to the Amazon RDS for SQL Server database instance

There are multiple ways to connect to Amazon RDS for SQL Server remotely using tools such as SQL Server Management Studio or other SQL Clients. However, it is important to ensure that your remote connections are secure while providing ease of access. We recommend using bastion hosts to delegate the connections to Amazon RDS for a number of reasons, including but not limited to security and cost savings.

In this approach, we deploy a bastion host to proxy the SQL Server connection from Systems Manager to the Amazon RDS for SQL Server instance, both deployed in a private subnet within a VPC. For instructions on using Systems Manager with the AWS Command Line Interface (AWS CLI), refer to the AWS Systems Manager User Guide.

The following diagram illustrates this architecture.

This approach works as follows:

You would configure your development environment which can be your VDI/Desktop or Laptop with AWS Command Line Interface and Session Manager Plugin with the proper API Key. We recommend using an IAM Role with SSM Permissions that you can assume from your environment
You can then deploy a bastion host or a jump host within the private subnet where your database instance is deployed and configure the bastion host security group to allow access to the database port. Also, ensure that at least one rule covers outbound 433 (HTTPS) so the VPC endpoints are reachable.
Similarly configure the security group for the Amazon RDS for SQL server instance to allow inbound traffic from the bastion host security group.
Create an instance profile with the permission policy SSMManagedInstanceCore and assign it to the bastion host.
Ensure that you have configured your local workstation or VDI with the right IAM user or assume an IAM role with the SSM permission.

Open a command prompt and enter the following AWS CLI command

aws ssm start-session `
--region <region> `
--target <bastion instance id> `
--document-name AWS-StartPortForwardingSessionToRemoteHost `
--parameters host="<rds endpoint>",portNumber="1433",localPortNumber="1433"

You should see a message similar to:

Starting session with SessionId: 12a3456bcdefghi789
Port 1433 opened for sessionId 12a3456bcdefghi789.
Waiting for connections...

You can now open SQL Server Management studio and connect to server name 127.0.0.1,1433 as this would forward the connections to the Amazon RDS for SQL Server instance through the bastion host. Make sure your connection is encrypted by configuring SSMS with the appropriate certificates and checking the Encrypt Connection checkbox in the connection properties tab.

Note that while the diagram depicts a Linux bastion instance, it is not required to be a Linux host as a windows instance would work just the same. However, given that the bastion exists solely to act as a proxy and pass through the connections to the RDS instance, the compute resource should be based on the most cost-efficient option.

This is a best practice when enabling remote access to an RDS for SQL Server instance for the following reasons:

The only ports that needs to be opened are between the bastion host and the RDS for SQL Server instance
IP allow listing is not required for remote desktop SSH connections
The Amazon Elastic Compute Cloud (Amazon EC2) bastion host can be in the private subnet without being exposed to the internet
It is recommended to have AWS Direct Connect, or AWS VPN for the connection between the VDI and AWS environment. Amazon EC2, Amazon RDS, and Systems Manager are all restricted from internet since the connection happens through AWS PrivateLink.

The methods discussed so far help with network security and ensure only selective traffic is allowed to reach your database. However, it’s equally important to further secure your RDS for SQL Server instance at the database level using various authentication and authorization methods that are available, as well as monitoring and auditing in place to ensure that any significant events pertaining to the database are monitored, logged, and available for further analysis and investigation. In the following sections, we discuss these key components for database security.

Database authentication

You can connect to the RDS for SQL Server database using either SQL or Windows Authentication. You may also use NTLM or Kerberos for Windows Authentication.

SQL Authentication

Within SQL Server, SQL Authentication keeps track of user name and password pairs. The only way to connect to Amazon RDS for SQL Server when it’s not a member of Active Directory (AD) is via SQL Authentication. SQL Server logins are less private because encrypted passwords and SQL Server login names are sent over the network when used, increasing the possibility of user information being stolen. Therefore, wherever feasible, use Windows Authentication over SQL Authentication.

Windows Authentication

You can enable Windows Authentication if an RDS for SQL Server instance is domain joined to an AD domain in a AWS Managed Microsoft AD. Enabling Windows Authentication allows the AD domain users to use their Windows credentials to access the SQL Server database. Depending on the use case, this may be a preferable way to manage access to the database, especially where human users need access for administration and running manual queries.

To allow domain users to log in to your RDS for SQL Server instance, ensure that the instance is domain joined to your AD domain and run the following query to create a login for the domain user:

USE [master] 
GO
CREATE LOGIN [mydomain\myuser] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; 
GO

After the login is created, the user can use their domain credentials to access the database. Ensure that an appropriate role is added to the user. In the next section, we discuss best practices to assign roles based on least privilege.

In some cases, multiple users may need access to a database, such as an application team where all members of the team require access to the database. In such cases, you can create an AD security group comprising all the users, and you can add the entire AD group to the RDS for SQL Server instance. With this approach, you can manage access to the database directly at the directory level by adding or removing users to the AD groups.

The following diagram illustrates this architecture.

In Windows operating systems, two popular client-server authentication methods are NTLM and Kerberos. However, Kerberos is preferable over NTLM for the following reasons:

It’s a two-part procedure that uses a ticket-granting service or a key distribution facility
It uses encryption and provides better security because it doesn’t cache or transfer the password over the network
Kerberos has built-in protection against MiTM (Man in the middle) attacks as well as replay attacks
Kerberos allows for reciprocal authentication, which can prevent some interception attacks and unlawful access

If Kerberos fails to authenticate the user, the system will fall back on NTLM provided if the endpoint is not a registered SPN and if its a registered SPN, it wont failback to NTLM instead it fails.

The following table summarizes the connection types.

Connection Type	SQL Server Connection String	Login Type	Auth_Scheme
RDS Endpoint	TestSQL.xxxxx.us-east-1.rds.amazon.com	Windows Authentication	NTLM
RDS Fully qualified domain name (FQDN)	TestSQL.octank.com	Windows Authentication	Kerberos
RDS Endpoint	TestSQL.xxxxx.us-east-1.rds.amazon.com	SQL Authentication	SQL
RDS Fully qualified domain name (FQDN)	TestSQL.octank.com	SQL Authentication	SQL

Note: Always On availability group listener doesn’t support Kerberos Authentication.

Database authorization

When it comes to Database authorization, it focuses on how to use roles and rights to limit authorization access to database data at the user level.

We highly advise against using the master user directly in your apps. Instead, adopt the best practice of creating a database user with the bare minimum of access necessary for your application. Create separate user accounts as a result, each user is only granted the rights required to perform their job obligations. Always use least-privilege security by employing role-based access (RBAC) constraints. RBAC is commonly used to enforce least privileges by giving users read-only access to databases.

Amazon RDS allows you to store and manage your master user credentials in AWS Secrets Manger throughout its lifecycle. AWS Secrets Manager enables the option to rotate secrets as often as every four hours, while offering the same managed rotation experience.

Following is some of the benefits of storing passwords in Secrets Manager.

RDS rotates database credentials regularly, without requiring application changes.
Secrets Manager secures database credentials from human access and plain text view.
You can monitor database credentials easily with AWS CloudTrail and Amazon CloudWatch.
Secrets Manager allows fine-grained control of access to database credentials in secrets using IAM.

If you inadvertently erase the primary user’s permissions, you can restore them by updating the database instance and entering a new primary user password. Also note that after the database instance is created, the primary user name cannot be changed.

Database access audit

When it comes to auditing the database, you can do so by enabling SQL Server Trace or SQL Server Audit.

SQL Server Trace

RDS for SQL Server instances have a default server-side trace utilizing T-SQL running on them, which can be accessible via a catalog view sys.traces, which provides the system’s current running traces. The key is the directory D:\rdsdbdata, which is the default location for Amazon RDS data, including logs. You can use the fn_trace_gettable function to read the trace file. You may also store server-side trace findings to a database table. See the following code:

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\, default);

You can create this table against any user database, and it will load the results of all files in the log directory, including all rollover files. Amazon RDS by default deletes trace and dump files older than 7 days. However, you can change the retention duration for trace files by using the rds_set_configuration stored method. For example, the following stored procedure changes the trace file retention time to 24 hours (1440 minutes):

exec rdsadmin..rds_set_configuration 'tracefile retention', 1440;

SQL Server Audit

Auditing an RDS for SQL Server instance or a single database entails tracking and reporting events that occur on the database engine. The native SQL Server Audit allows you to design server audits that can include server audit specifications for server-level events, as well as database audit specifications for database-level events. Audited events are always recorded into the audit files.

With option groups, you can enable auditing in your RDS for SQL Server instance. Note that you must create a new option group and add an option of sqladuit because Amazon RDS doesn’t allow you to modify the default option group.

Before these audit files are deleted and migrated to Amazon Simple Storage Service (Amazon S3), you will be offered the option of keeping them on the disk. Audit files are saved in your account’s S3 bucket. The audit files are compressed before being saved on Amazon S3, which helps reduce Amazon S3 costs. Defining an IAM role is important because it grants Amazon RDS the permission to write files to your bucket.

Audit files should not begin with RDS_ because this is a reserved name area for FedRAMP and HIPAA audits.

The file size is recommended to be within the limit range of 2–50 MB; otherwise, the copy of these files to Amazon S3 may be impacted. AWS saves and archives audit logs for the specified retention period. By default, the retention option is disabled, indicating that AWS deletes audit logs once they are offloaded to the specified S3 bucket. You can change this behavior by setting the value of your choice, anywhere between 1–840 hours.

Use the stored procedure rds_fn_get_audit_file to retrieve information from an audit file created by a server audit in SQL Server. In case of audit failure, you can choose between the following options: No shutdown instead continue or Fail operation.

Server-level audits are supported by all editions of SQL Server. With SQL Server 2016 (13.x) SP1, all versions allow database level audits. Previously, database-level auditing was only available in Enterprise, Developer, and Evaluation editions.

Amazon RDS for SQL Server now supports database activity streams, which allow you to see a near-real-time stream of database activity such as failed logins in your relational database. For more information, see Audit Amazon RDS for SQL Server using database activity streams

Database encryption

In this section, we discuss data encryption in transit, encryption at rest, and transparent data encryption (TDE).

Data encryption in transit

You can use Secure Sockets Layer (SSL) to encrypt communications between client apps and RDS database instances running SQL Server. You can achieve this by enabling the rds.force_ssl parameter through the parameter groups. By default, the rds.force_ssl parameter is set to 0 (off). Set the rds.force_ssl parameter to 1 (on) to force connections to use SSL. The rds.force_ssl parameter is static, so after you change the value, you must reboot your database instance for the change to take effect. SSL/TLS connections add a layer of security by encrypting data sent between your client and a database instance. By confirming that the connection is being made to an RDS database instance, a server certificate adds an added degree of protection. It accomplishes this by inspecting the server certificate, which is deployed automatically on all database instances that you create.

You can use SSL to connect to your RDS for SQL Server database instance in two ways:

Enforce SSL for all connections – This occurs invisibly to the client, and the client doesn’t need to do anything to utilize SSL
Encrypt individual connections – This establishes an SSL connection from a specific client computer, and encrypting connections requires effort from the client

You can use the following command to know if the connection is encrypted or not:

select ENCRYPT_OPTION from SYS.DM_EXEC_CONNECTIONS where SESSION_ID = @@SPID

To encrypt connections from SQL clients running on application servers, append encrypt=true to your connection string. You may need to add the Amazon RDS for SQL Server certificate to the Java CA certificate (cacerts) repository to allow SSL encryption for clients connecting through JDBC. This is possible with the keytool utility. For more information about downloading certificates, see Using SSL/TLS to encrypt a connection to a DB instance.

Data encryption at rest

You have two choices for enabling encryption at rest:

Configure encryption at rest using AWS Key Management Service (AWS KMS) encryption keys.
Utilize transparent data encryption (TDE) if you’re running SQL Server 2019 Enterprise edition or Standard edition. Note that prior to SQL Server 2019, the TDE feature was available only on Enterprise edition.

During database instance creation, you can set up AWS KMS to allow encryption with data at rest. When you create an encrypted database instance, you may encrypt it with either a client-controlled key or an AWS managed key for Amazon RDS. If you don’t supply a key identifier for a customer managed key, Amazon RDS creates a new database instance using the AWS managed key. Amazon RDS generates an Amazon RDS managed key for your AWS account. For each Region, your AWS account has a unique AWS managed key for Amazon RDS.

You can’t alter the AWS KMS key used by an encrypted database instance after it has been built. As a result, when you establish your encrypted database instance, be sure to identify your AWS KMS key needs.

AWS KMS combines secure, highly available hardware and software to deliver a cloud-scaled key management solution. You may build customer managed keys and specify the policies that govern how these customer managed keys can be used using AWS KMS. Because AWS KMS supports CloudTrail, you may audit AWS KMS key usage to ensure that customer managed keys are utilized correctly.

For more information about the limitations of Amazon RDS encrypted database instances, refer to Encrypting Amazon RDS resources.

Transparent data encryption

Transparent data encryption (TDE) is also supported by Amazon RDS for encrypting SQL Server database instances. You can use TDE in conjunction with Amazon RDS encryption at rest, but doing so may have a little impact on your database’s performance. You must have separate keys for each encryption technique.

TDE automatically encrypts data before writing it to storage and decrypts data when it is read from storage. It manages encryption keys with a two-tier key architecture. To safeguard the data encryption keys, a certificate issued from the database primary key is employed. The database encryption key is responsible for encrypting and decrypting data on the user database. Amazon RDS stores and administers the database primary key as well as the TDE certificate.

If the database instance isn’t linked with a TDE-enabled option group, you have two options: you can add the TDE option by creating an option group or by modifying the corresponding option group.

Unencrypted database performance may suffer if the databases are on a database instance with at least one encrypted database. As a result, we recommend that you retain encrypted and unencrypted databases on different database instances.

You must add the SQLSERVER BACKUP RESTORE and TRANSPARENT DATA ENCRYPTION options to the option group associated with your database instance.

TDE certificates may be backed up, restored, and dropped using RDS for SQL Server stored procedures. Amazon RDS for SQL Server has the ability to examine recovered user TDE certificates.

Additional strategies

You can also use the following strategies to safeguard data:

Row-level security – Use row-level security (RLS) if you want to restrict database users access to only the data rows relevant to their department or the business. RLS assists you in implementing data row access controls. RLS makes it easier to design and code security in your application. It removes the dependency of maintaining the access restriction mechanism in the application tier; instead, the database system acts as a placeholder to implement these access restrictions. With the right table-valued filter function and the security policy in place, you can implement RLS to enhance the solidity and strength of your security design. RLS is supported across all Amazon RDS for SQL Server editions starting at 2016 (13.x , SP1, and above).
Data masking – Data masking is another data security strategy that allows you to obscure sensitive information so that it is useless if it’s accessed by an intruder. It protects sensitive data by masking it from non-privileged users. The database owner by default is an exception to this. The masked data is used to replace original data in proofs of concept or any other use cases when the data itself is not required. Again, the data masking mechanism is done at the database level, allowing applications to conceal sensitive data without having to alter current queries. There are numerous masking formats available; choose the format based on your individual needs for various sensitive data categories. Data masking is supported across all Amazon RDS for SQL Server editions starting at 2016 (13.x , SP1, and above).
Always encrypted – This data encryption approach aids in the protection of sensitive information during data transfer between the application and the database server, data at rest, and while the data in use. By transforming plain text into an encrypted format, it guarantees that sensitive data is always shown as encrypted within the database system. It’s important to note that this approach only encrypts the column you select, not the whole table or database. You can effectively encrypt the columns using the appropriate encryption technique and cryptographic keys (column encryption key and column primary key). You can comply with your organization’s rules or compliance laws by rotating the encryption keys on a regular basis. Note that encrypted columns take significantly more space. This feature is supported across all Amazon RDS for SQL Server editions starting at 2016 (13.x, SP1, and above). For more information refer to Set up Always Encrypted with Amazon RDS for SQL Server.
SQL Server triggers – Triggers are the objects that run a stored set of instructions when certain criteria are satisfied. You can use these to avoid unauthorized updates to any objects, creating new login credentials using DML, DDL triggers, or to limit the total number of connections against Amazon RDS for SQL Server instance using logon triggers. You can use these triggers in combination with Amazon CloudWatch alarms to send an automatic email when a security policy is violated.
Column-level encryption – This approach encrypts data at a more granular level and can be applied to all or selected columns. Column-level encryption allows you to specify distinct encryption keys for each column. For more information, see Column-level encryption on Amazon RDS for SQL Server

Database monitoring

You can use CloudWatch to identify abnormal activity in your environments, create alerts, perform automatic actions, and resolve issues. Make sure to select the Log exports option so error and SQL Agent logs are published to CloudWatch Logs.

You can publish unauthorized login data or any errors reported in the error log of the RDS for SQL Server instance to CloudWatch. We can use Amazon Simple Notification Service (Amazon SNS) to provide notifications to end-users based on CloudWatch log groups, patterns, and alarms.

Note that publishing SQL Server logs to CloudWatch Logs isn’t enabled by default. Additionally, publishing trace and dump files isn’t supported. Publishing SQL Server logs to CloudWatch Logs is supported in all Regions, except for Asia Pacific (Hong Kong)(as of this writing).

The following diagram shows an example architecture using CloudWatch.

To learn more, refer to Best Practices on how to configure Monitoring and Alerts for Amazon RDS for SQL Server.

Additional AWS services

You can also use the following services to help build your security framework:

AWS Trusted Advisor – AWS Trusted Advisor recommends core security best practices curated by security professionals, which may help improve the security of your AWS environment. For example, Trusted Advisor can identify RDS security group access risks, unprotected access keys, unneeded S3 bucket permissions, and MFA on the root account.
Amazon Macie – Amazon Macie is a fully managed data security solution that employs machine learning and pattern matching to assist you in discovering and protecting sensitive data in your RDS for SQL Server instance. The procedure is meant to capture snapshots of Parquet-compressed databases and store them in Amazon S3. Then you can start the Macie task, which searches for sensitive data. You can then read and analyze the classified data with Amazon Athena and Amazon QuickSight.

Summary

In this post, we provided a comprehensive overview of how to properly use Amazon RDS for SQL Server, including best practices for connection, networking, and various security choices. You can decide on the appropriate security method for your databases based on the review of the solutions provided. For more information about TDE certificate rotation, see TDE certification rotation on Amazon RDS for SQL Server . For more information about Customizing security parameters, see Customizing security parameters on Amazon RDS for SQL Server.

To learn more about Amazon RDS security, refer to Security in Amazon RDS.

Leave a comment if you have any questions or recommendations concerning this topic.

About the Authors

Suprith Krishnappa C is a Database Consultant with the Professional Services team at Amazon Web Services. He works with enterprise customers, offering technical support and designing customer solutions on database projects, as well as assisting them in migrating and modernizing their existing databases to the AWS cloud.

Santhosh Srinivasan is a Sr. Cloud Application Architect with the Professional Services team at Amazon Web Services. He specializes in building and modernizing large scale enterprise applications in the cloud with a focus on financial services industry.

AWS Database Blog