Protect Your SQL Servers with Always Encrypted

by Dec 2, 2020

The information stored in corporate databases needs to be protected in multiple ways. Database backups are necessary to recover from a physical or logical disaster requiring systems to be rebuilt. Firewalls and other network security measures need to be taken to keep unauthorized users from accessing data resources. Access to sensitive data must be restricted to those who need it and even then should be monitored for potential abuse.

As anyone who has been in IT for any length of time knows, things don’t always work out the way they were planned. Firewalls can fail or be compromised and an innocent oversight may expose sensitive data to numerous individuals. Backup media can be stolen, putting all the information they contain at risk. Despite best efforts, there is always a chance that enterprise data assets will fall into the wrong hands.

Encryption Provides Additional Protection

One of the most effective methods of implementing and maintaining data security is encryption. In simple terms, encryption is the process of taking information in human-readable form (plain text) and transforming it into an unreadable format (ciphertext). The encoded data has to be decrypted before it can be read or processed. Encryption blocks data from view while decryption reveals it. 

An encryption key, which is a unique and random numerical sequence used by the encryption algorithm, is necessary to decrypt the data. Encryption can be symmetric, in which a single password is used for the encoding and decoding procedures. Two keys are used for asymmetric encryption. Data is encrypted with a shared public key and decrypted using a private key that is not shared.

Data exists in three distinct states which present different challenges for those attempting to keep it secure.  

  • Data-at-rest is stored on a physical device like a disk drive. This type of data is the easiest to protect with techniques like full disk encryption. The problem is that for data to be useful, it cannot remain at rest forever. 
  • Data-in-motion is moving through a network. Examples of data-in-motion are uploading and downloading files from the cloud or sending emails. Data is vulnerable while in motion and needs to be encrypted to make it unusable if it is intercepted by malicious entities.
  • Data-in-use is currently being accessed or processed. This class of data is seen in active databases or an open file and poses challenges from an encryption perspective. The data must be in a form that can be viewed by the user or application that requested it. Keeping this type of data secure is often done through identity management and tightly controlling access.

How Always Encrypted Protects Your SQL Server Data

Microsoft’s Always Encrypted is a feature that is incorporated into SQL Server versions since SQL Server 2016 and is also available in Azure SQL Database. Its purpose is to protect sensitive data by allowing clients to encrypt data inside client applications without exposing the encryption keys to the database engine. In this way, there is a separation between the data owners who have the right to view it and the team that manages the information but should not be able to access it.

One of the weak points in providing security for sensitive data resources is the level of permission and access that DBAs and system administrators need to perform their jobs. Insider data breaches are a growing problem that can be difficult to fully address using traditional security and encryption techniques. Always Encrypted offers a method for ensuring that data remains encrypted at all times.

Two types of encryption can be implemented using Always Encrypted. Deterministic encryption can be queried and indexed. It’s good for data that encompasses large-scale value ranges. Randomized encryption makes data unidentifiable by assigning different values for similar data. This type of encryption is more secure but cannot be searched.

An IDERA webcast should be required viewing for teams interested in implementing Always Encrypted on their SQL Servers. It explains the inner workings of the encryption method and will help make the correct choices when configuring Always Encrypted for your environment. The most important factor to be considered is how the data will be used. Many of the other decisions related to implementation flow from the answer to that question.

Identifying SQL Server Security Flaws

It is impossible to effectively protect a SQL Server environment without a thorough knowledge of how permissions are granted and who has access to sensitive data. The complications involved with granting access to SQL Server make it important to have the right software tools to ensure security is fully implemented. Leaving the process up to manual methods just provides another potential point of failure.

IDERA’s SQL Secure offers teams a valuable solution that can help lock down a SQL Server environment with advanced security analysis and reporting capabilities. Identify and analyze powerful group membership to verify that the level of access is required. Predefined templates define guidelines to protect systems from common intrusion attacks. Risk assessment can be performed on database access, permissions, and configuration.

Database security is a multi-faceted undertaking that will benefit from a combination of Always Encrypted and SQL Secure. You can’t be too careful with sensitive enterprise data. It pays to use all the tools at your disposal to ensure that only the right people have access to data resources.