SQL Server Data Encryption at Rest – SQL Azure DB

What is encryption at rest?

Encryption is the process of translating one form of data into another form of data that unauthorized users can’t decrypt. By encrypting data at rest, it is essentially converting sensitive data into another form of data (encrypted data). It requires an algorithm that can’t be accessed without an encryption key to decode it. The Encryption at Rest designs in Azure utilizes a symmetric encryption method to encrypt and decrypt large amounts of data more swiftly according to a simplistic conceptual pattern:

• A symmetric encryption key is used to encrypt data while being written into the storage.
• The same encryption key is used to decrypt that data as it is already in the memory.
• Chunks of data or partitioned data can be made, and different keys assigned to be used for each partition.
• Keys are stored in a secure location with identity-based access control and audit policies. Data encryption keys are often encrypted with a key-encryption key in Azure Key Vault to further strengthen security and limit access.

Where as, data encryption is a transformation of data into another form to improve security. The people who have access to secret or decryption key or password can only read. The main purpose of it is to protect the confidentiality of digital data.

SQL server encryption at rest – The SQL server encryption is a process to encrypt connections (i.e. links), data and procedures that are stored in a database. The various areas that are needed to be covered to secure SQL Server are the platform, authentication, objects mainly data and applications that access the system.

The various SQL Server encryption options are Transparent Data Encryption (TDE), Column-level Encryption, Encrypting and Decrypting Data with the.NET Framework, Encrypting File Systems, and BitLocker.

Update: All lately created databases in SQL databases are encrypted by default by utilizing service-managed transparent data encryption. Existing SQL databases produced before May 2017 and SQL databases created through restore, geo-replication, and database copy are not encrypted by default. Existing SQL Managed Instance databases created before February 2019 are also not encrypted by default. SQL Managed Instance databases created through restore inherit encryption status from the source.

How does TDE encryption work? and What is Transparent Data Encryption in SQL Server?

Transparent Data Encryption (TDE) is the primary encryption option that was made available in SQL Server 2008. It enables us to encrypt the whole database. The backups for databases using TDE are also encrypted and it protects the data at rest. It is easy for implementation as well. It encrypts the data stored in both the database’s data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. TDE cannot be used to encrypt the master database in SQL Database. The master database contains objects that are needed to perform the TDE operations on the user databases.

Like data compression, TDE database encryption is performed at the page level. Data is encrypted on the disk and is decrypted as it’s read into memory. When we perform the encryption at the page level, it enables the encryption process to be completely transparent to the client applications. It doesn’t have many limitations on the searching ability or query the data in the encrypted database. In addition, since most database applications are optimized to minimize input/output for performance reasons, the encryption process becomes efficient.

The participating systems become encrypted if the database is being used with AlwaysOn Availability Groups, database mirroring, or log shipping. The main point is that TDE encrypts the stored data but doesn’t encrypt the communications link between the server and the client applications. If we need to encrypt the data connection between the application and the server, we need to use an SSL connection for clients. Technologies such as database mirroring and AlwaysOn Availability Groups support network transport encryption as endpoint properties.

What is Always Encrypted?

Always Encrypted is a feature that is designed to protect sensitive data, such as credit card numbers or national identification numbers, stored in SQL Server databases. The sensitive data inside client applications can be encrypted by clients and the encryption keys are never revealed to SQL Server. As a result, it provides a separation between those who own the data and those who manage the data.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatic encryption and decryption of sensitive data in the SQL Server client application. The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically queries are rewritten so that the semantics of the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Manage transparent data encryption through Azure Portal

To get started with managing TDE through the Azure portal, you must connect to the portal as the Azure Owner, Contributor, or SQL Security Manager.

Enable and disable TDE on the database level. For Azure SQL Managed Instance use Transact-SQL (T-SQL) to turn TDE on and off on a database. For Azure SQL Database and Azure Synapse, you can manage TDE for the database in the Azure portal after you’ve signed in with the Azure Administrator or Contributor account. Find the TDE settings under your user database. By default, service-managed transparent data encryption is used. A TDE certificate is auto-generated for the server that contains the database.

You set the TDE master key, known as the TDE protector, at the server or instance level. To use TDE with BYOK support and protect your databases with a key from Key Vault, open the TDE settings under your server.

Disadvantages of Transparent Data Encryption (TDE) compared to Always Encrypted:

1. Only protects data at rest – backups and data files are “safe” but data in motion or in memory is vulnerable.

2. Only complete database.

3. All data is encrypted the same way.

4. Requires Enterprise Edition.

5. Data always accessible to a system administrator.

The future of encryption and the innovation of the applications providers of the near future will forever influence how enterprises conduct business electronically. It begs the question, “What would we do if all our information were safe?”

Need help with Microsoft Azure? Check out our cloud migration, development and maintenance services! Click below to get in touch!