Let’s discuss running SQL Server on Amazon RDS and security best practices. We already reviewed some availability best practices, and now will focus on security best practices. We will divide it into access restriction, audit, and data encryption.
Access restriction (VPC, Security Groups, Subnets)
We can restrict access at different levels.
First, creating an individual IAM (WS Identity and Access Management) user for each person who manages Amazon RDS resources, including yourself. For example, you can create a user that can have access only to create, stop, and start an instance but not to delete.
Do not use AWS root credentials to manage Amazon RDS resources. Grant each user the minimum set of permissions required to perform his or her duties. Rotate your IAM credentials regularly. Configure AWS Secrets Manager to automatically rotate the secrets for Amazon RDS.
On another level, use Amazon VPC (Amazon Virtual Private Cloud) to provide a logically isolated section of the AWS Cloud. You will have complete control over your virtual networking, including the creation of subnets, selection of your IP address range, network gateways, and so on. Place databases or application servers in a private-facing subnet with no internet access, for example.
Access Audit (SQL Server trace, SQL Server Audit)
To make auditing easier, use IAM groups, consistent naming schemes, and straightforward policies.
In Amazon RDS, you can easily audit Microsoft SQL Server databases by using the built-in SQL Server auditing mechanism. In the same way that you create them for on-premises database servers, you can create audits and audit specifications.
SQL Server 2008 and 2012/2014 Standard/Web/Express editions have some nuances on audit, so in these cases, we should use the SQL Server trace.
However, if there is a possibility, it’s always better to use a newer SQL Server version, since SQL Server trace will be discontinued soon.
Data encryption (SSL, KMS, TDE)
AWS supports encryption at rest and in transit.
Encryption at rest is supported by storage encryption using AWS key management service (KMS) or if it’s Enterprise Edition -transparent data encryption (TDE) can be used. They support both options, both are transparent adaptive.
Amazon RDS service works without additional protection, but if you require encryption or data integrity authentication of data at rest for compliance or other purposes, you will need to add protection at the application layer, or at the platform layer using SQL cryptographic functions.
Tor example, at the application layer, add protection using a built-in encryption function that encrypts all sensitive database fields (using an application key), before storing them in the database. The application can manage keys by using symmetric encryption with PKI infrastructure or other asymmetric key techniques to provide for a master encryption key.
Platform-level encryption keys would be managed at the application level, like application-level encryption keys. In this case, Microsoft SQL Microsoft Transact-SQL data protection functions include encryption, signing, and hashing.
