AWS Database Blog

Secure your applications running on Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server supports several security features that can help you secure your application data on AWS. These features protect your data both in transit and at rest, while also providing separation of duties and auditing capabilities. The majority of these features are built into SQL Server and can be used to satisfy your regulatory requirements.

Given that security is, and will continue to be, a top priority for many organizations, we aim to show you the most effective ways of utilizing these features, taking into consideration the use cases of modern applications.

In this post, we discuss the various security features available on Amazon RDS for SQL Server:

  • Column-level encryption
  • Always Encrypted
  • Dynamic data masking
  • Row-level security
  • Transparent data encryption and Amazon RDS encryption
  • SQL Server Audit
  • Database activity streams

We also delve into the steps of how to use dynamic data masking and row-level security on Amazon RDS for SQL Server.

Column-level encryption

As part of your business requirements, you may need to encrypt one or more columns in a table. These columns could contain personally identifiable information (PII) data, such as identification number, driver’s license number, and credit card number.

Column-level encryption was introduced in SQL Server 2005 to provide a lower granularity of control to encrypt specific columns that contain private information. There are two methods to enable column encryption: symmetric keys and asymmetric keys. For more information, refer to Column-level encryption on Amazon RDS for SQL Server.

A few considerations when using column-level encryption:

  • You need to use the EncryptBy and DecryptBy functions to encrypt and decrypt the specific columns. This will result in code changes.
  • Reading encrypted data is resource intensive and may cause performance degradation of the database.
  • The encrypted value is non-deterministic, therefore using an encrypted column in your query filter (WHERE clause) may not yield a better performance.

Always Encrypted

You may have a requirement to encrypt your data on the application side and want a separation of duties, such as the database administrator (DBA) can’t view the data stored in the database. Changing the code isn’t an option, so column-level encryption isn’t possible.

Always Encrypted was introduced in SQL Server 2016, makes encryption transparent to the client application. It allows the client to encrypt sensitive data inside the client application through an Always Encrypted enabled driver before storing the encrypted data in the database. This provides separation between data owners and the team that manages the data, like DBAs.

Always Encrypted uses two types of keys:

  • Column primary key – This key is used to protect the column encryption key. The database engine stores the metadata of this key that points to the key’s location, which is stored in an external key store. An example can be the Windows certificate store on the client server.
  • Column encryption key – This key is used to encrypt and decrypt the encrypted columns. The database engine stores the encrypted value of the key.

Considerations when using Always Encrypted:

  • Deterministic encryption supports queries that perform point lookups, equality joins, grouping, and indexing. However, it may allow unauthorized users to guess the information about the encrypted value by comparing patterns of the encrypted column, especially if the column contains a small set of possible values.
  • Randomized encryption is more secure but doesn’t support searching, grouping, indexing, and joining of encrypted columns.

For more information, refer to Set up Always Encrypted with Amazon RDS for SQL Server.

Dynamic data masking

You might have a call center application where you want to prevent the call center’s personnel from gaining full access to the data stored in the credit card number column. You want to expose just the last 4 digits of the credit card number for verification purposes.

Dynamic data masking (DDM) helps prevent unauthorized access to sensitive data by enabling security personnel and database administrators to specify how much sensitive data to reveal with minimal impact to the application. You can configure DDM on specific table columns to hide the information without changing the data itself. This allows you to do business with customers in a secure way by limiting exposure of the data.

A data masking policy acts directly on sensitive columns in the table for a defined set of users. The masking function is applied in real time to query results based on the policy. Users with UNMASK permission can retrieve unmasked data from the columns where masking is defined.

Considerations of DDM:

  • Users with write permission can update masked columns. Proper access control policies should be in place to limit update permissions.
  • Using SELECT INTO and INSERT INTO statements results in masked data in the target table.
  • The Import/Export wizard results in masked data in the target database (if run by a user without the UNMASK permission).

In the following sections, we show you how to implement DDM on Amazon RDS for SQL Server. The DDM feature was introduced in SQL Server 2016 to provide the capability to mask sensitive data, preventing data exposure to nonprivileged users. You can configure DDM to hide sensitive data of specific columns in the returned result set. This feature has minimal effect on the application.

Prerequisites

You must have the following prerequisites:

  • An RDS for SQL Server instance (SQL Server 2019 Enterprise or Standard Edition)
  • A database to use for this solution
  • An Amazon Elastic Compute Cloud (Amazon EC2) Windows instance with SQL Server Management Studio (SSMS) installed

Implement DDM on Amazon RDS for SQL Server

The following are the implementation steps:

  1. Connect to Amazon RDS for SQL Server using SSMS installed on your client EC2 Windows machine.
  2. Open a new query window.
  3. Create a table and insert some data:
CREATE TABLE Customer (
Cust_ID INT IDENTITY(1, 1) PRIMARY KEY
,Cust_First_Name NVARCHAR(10) NOT NULL
,Cust_Last_Name NVARCHAR(10) NOT NULL
,Cust_Date_Of_Birth DATETIME NULL
,Cust_CCard VARCHAR(20) NULL
,Cust_Email NVARCHAR(50) NULL
)

INSERT INTO dbo.Customer
VALUES ('John','Doe','1-Apr-1960','1234-5678-9012-3456','john.d@xyz.com')
INSERT INTO dbo.Customer
VALUES ('David','Tan','16-Aug-1975','4211-5678-8099-2277','tan.david@abc.com')
INSERT INTO dbo.Customer
VALUES ('Mike','Cool','31-Oct-1980','5221-9990-9015-8769','mikecool@def.com')
  1. Create a test user that has read-only access to the table:
CREATE USER DDMUser WITHOUT LOGIN;
GRANT SELECT ON Customer to DDMUser;
  1. If the user selects from the table, the person can see the sensitive data:
EXECUTE AS USER = 'DDMUser';
SELECT * FROM dbo.Customer;
REVERT

DDM-Unmask

  1. Mask the sensitive information using the following command (for this example, we mask two columns in the table):
ALTER TABLE dbo.Customer
ALTER COLUMN Cust_CCard ADD MASKED WITH (FUNCTION='partial(0,"XXXX-XXXX-XXXX-",4)')
ALTER TABLE dbo.Customer
ALTER COLUMN Cust_Email ADD MASKED WITH (FUNCTION='email()')
  1. The user can retrieve the data again, but this time those columns are masked:
EXECUTE AS USER = 'DDMUSer';
SELECT * FROM dbo.Customer; 
REVERT;

DDM-Mask

As discussed, the underlying data remains unchanged.

  1. The user can view the masked data if granted the UNMASK permission:
GRANT UNMASK TO DDMUser;
GO
EXECUTE AS USER = 'DDMUSer';
SELECT * FROM dbo.Customer;
REVERT;
GO
REVOKE UNMASK TO DDMUser
EXECUTE AS USER = 'DDMUser';
SELECT * FROM dbo.Customer;
REVERT;

DDM-Compare

Row-level security

For this use case, you’re developing a multi-tenant application where all your customers are sharing the same database and tables. Your goal is to ensure that your customers access only data that is pertinent to their organization.

Row-level security (RLS) allows data access restriction to happen on the database tier, rather than on the application layer. RLS uses the execution context of the user to control access to the records in the underlying table.

RLS supports two types of security predicates:

  • Filter predicate – Transparently filters the records available to read operations, such as SELECT, UDPATE, and DELETE
  • Block predicate – Blocks write operations that violate the policy

Access to the data is restricted by applying the security predicate that is being defined as a function. The function is invoked and enforced by a security policy.

Considerations of implementing RLS:

  • The user or application can still perform an insert, even if the row is filtered during other operations
  • Rows are filtered even when queried by the table owner if a security policy is defined
  • The security policy manager doesn’t require SELECT permission on the table
  • You should avoid excessive table joins in predicate functions to optimize performance

In the following sections, we show you how to implement RLS on Amazon RDS for SQL Server. Row-level security was introduced in SQL Server 2016 and provides fine-grained access role control over your data. It is transparent to the application or user and provides centralized access management within the database.

Prerequisites

You need the following prerequisites:

  • An RDS for SQL Server instance (SQL Server 2019 Enterprise or Standard Edition)
  • A database
  • An EC2 Windows instance with SSMS installed

Implement RLS on Amazon RDS for SQL Server

The following are the implementation steps:

  1. Connect to Amazon RDS for SQL Server using SSMS installed on your client EC2 Windows machine.
  2. Open a new query window.
  3. Create three database users, which we use later to simulate RLS:
CREATE USER DBAManager WITHOUT LOGIN; 
CREATE USER DBA1 WITHOUT LOGIN;
CREATE USER DBA2 WITHOUT LOGIN;
GO
  1. Create a schema and table, and insert some data:
CREATE SCHEMA HR
GO
CREATE TABLE HR.Employee
(
EmpID int,
EmpName nvarchar(50),
Salary int
);

INSERT INTO HR.Employee VALUES (1, 'DBA1', 80000);
INSERT INTO HR.Employee VALUES (1, 'DBA2', 85000);
  1. Grant SELECT permission on the table to the three users:
GRANT SELECT ON HR.Employee TO DBAManager;
GRANT SELECT ON HR.Employee TO DBA1;
GRANT SELECT ON HR.Employee TO DBA2;
  1. Create the security function and policy that will be evaluated against the role of the user:
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.tvf_securitypredicate(@DBA AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @DBA = USER_NAME() OR USER_NAME() = 'DBAManager';
GO
CREATE SECURITY POLICY SalaryFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(EmpName)
ON HR.Employee
WITH (STATE = ON);
GO
  1. Grant access to the function:
GRANT SELECT ON Security.tvf_securitypredicate TO DBAManager;
GRANT SELECT ON Security.tvf_securitypredicate TO DBA1;
GRANT SELECT ON Security.tvf_securitypredicate TO DBA2;
  1. Simulate RLS using the EXECUTE AS feature:
EXECUTE AS USER = 'DBA1';
SELECT * FROM HR.Employee;
REVERT;
EXECUTE AS USER = 'DBA2';
SELECT * FROM HR.Employee;
REVERT;
EXECUTE AS USER = 'DBAManager';
SELECT * FROM HR.Employee;
REVERT;

You will notice that DBA1 and DBA2 will only be able to query their own data, whereas DBAManager is able to pull data of both DBAs.

Transparent data encryption and Amazon RDS encryption

For this use case, you need to enable data encryption at rest to meet regulatory requirements, such as PCI DSS compliance.

Amazon RDS encryption provides a similar data encryption at rest capability. Data is encrypted at rest, which includes its underlying storage, automated backups, read replicas, and snapshots. Amazon RDS encryption uses the industry standard AES-256 to encrypt your data and handles authentication of access and decryption of your data transparently with minimal impact to performance.

SQL Server transparent data encryption (TDE) was introduced in SQL Server 2008 and is another option to encrypt your data at rest. It encrypts the data and log file of your database, which prevents malicious parties from restoring your database from stolen drives or backup tapes. TDE is implemented with layers of encryption hierarchy. The database encryption key (DEK) is stored in the boot record of the database for recovery purposes. The DEK is protected by a certificate that is stored in the primary database. The following figure shows the encryption hierarchy.

TDE

Considerations of implementing TDE:

  • Back up the certificate and database primary key when TDE is enabled. Keep them in a secure location.
  • Do not drop the certificate that is used to protect the DEK; this would make the TDE-enabled database inaccessible.
  • When TDE is enabled on a single user database, the tempdb is encrypted as well. This encryption might have a performance effect on other non-encrypted databases in the same instance.

When deciding between TDE and Amazon RDS encryption, consider the following:

SQL Server Audit

You may have a regulatory requirement to audit changes made to the database instance, both at the database level and instance level. For example, you might want to track changes to the underlying tables or configuration changes of the instance.

Amazon RDS for SQL Server supports the native SQL Server Audit feature which was introduced in SQL Server 2008. It allows you to track and log events that occurred on the instance. SQL Server Audit lets you create server audit specifications and database audit specifications for both server-level and database-level events.

Because Amazon RDS for SQL Server is a fully managed database service, to provide you a fully managed experience, the audit files and logs can be stored in an Amazon Simple Storage Service (Amazon S3) bucket that belongs to you. To use SQL Server Audit, you have to enable the feature through the options group.

There are four components to SQL Server Audit:

  • SQL Server Audit – An object that collects a single instance server-level or database-level action or group of actions to monitor.
  • Server audit specification – An object that belongs to SQL Server Audit. This allows you to define the actions to monitor at the instance level, such as configuration changes.
  • Database audit specification – An object that belongs to SQL Server Audit. This allows you to define the actions to monitor at the database level, such as schema changes to the tables or indexes.
  • Target – The results of an audit are sent to a target. For Amazon RDS, the target would be a file that is stored in an S3 bucket.

Refer to SQL Server Audit for information about how to use and configure SQL Server Audit on Amazon RDS.

Database activity streams

Database activity streams (DAS) now supports Amazon RDS for SQL Server to provide a near-real-time stream of database activities for auditing and compliance purposes. You can integrate DAS with your monitoring tools in order to monitor and set alarms for auditing the database activity. You can also connect Amazon Kinesis Data Streams to Amazon Kinesis Data Firehose to save stream logs in a user-readable format to Amazon S3.

You can enable DAS with only a few clicks on the AWS Management Console to provide safeguards for your databases and help you meet compliance and regulatory requirements. Refer to Audit Amazon RDS for SQL Server using database activity streams to learn how to enable DAS.

Summary

Cloud security at AWS is the highest priority. Security is a shared responsibility between AWS and you, the customer. The shared responsibility model describes this as security of the cloud and security in the cloud. Amazon RDS for SQL Server provides a set of features to ensure that your data is securely stored and accessed. For additional security best practices please refer to Secure your data with Amazon RDS for SQL Server: A guide to best practices and fortification. Leave a comment if you have any questions.

About the Authors

Barry2Barry Ooi is a Senior Database Specialist Solutions Architect at AWS. His expertise is in designing, building, and implementing data platforms using cloud-native services for customers as part of their journey on AWS. His areas of interest include data analytics and visualization. In his spare time, he loves music and outdoor activities.

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.