SQL Server Security

[Security Best Practices] SQL Server On Amazon RDS

Mark Varnas
No comments

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.

  1. Do not use AWS root credentials to manage Amazon RDS resources. 
  2. Grant each user the minimum set of permissions required to perform his or her duties. 
  3. Rotate your IAM credentials regularly. 
  4. 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.

For 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.

Agree? Disagree? Comment below!

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials