- SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)
- SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted
- SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)
- General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases
- SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)
Introduction
With the advent of Better quality of care, business data security concerns are growing, and an area that was once overlooked is in evidence more than ever now. As a result, IT professionals, especially DBAs, have been looking for ways to reduce the risk of data exposure and one way to do this is to encrypt data to prevent unauthorized access by third parties.
My idea in this article is to demonstrate a SQL Server solution that lets you encrypt data, which is the Transparent Data Encryption (TDE), available since SQL Server version 2008 in Enterprise and Developer editions.
Be sure to check out my post SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted, another SQL Server data encryption solution, available since the 2016 release in Express, Standard, Enterprise, and Developer editions.
For the examples below, I will use the following script for base generation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
IF (OBJECT_ID('dbo.Pessoa') IS NOT NULL) DROP TABLE dbo.Pessoa CREATE TABLE dbo.Pessoa ( [Name] NVARCHAR(101), [BirthDate] DATE, [EmailAddress] NVARCHAR(50), [Phone] NVARCHAR(20) ) INSERT INTO dbo.Pessoa VALUES |
Transparent Data Encryption (TDE)
Transparent Data Encryption (TDE) encrypts SQL Server data files, which is known as at rest data encryption. In a scenario where physical media (such as backup drives or tapes) is stolen, a malicious third party can restore or attach the database and browse the data. One solution to this is to encrypt sensitive data in the database and protect the keys used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this type of protection must be planned in advance.
TDE performs real-time I / O encryption and decryption of data and log files and protects data on disk, ie data and log files, providing the ability to comply with many laws, regulations and guidelines established in various sectors. This allows software developers to encrypt data using AES and 3DES encryption algorithms without changing existing applications.
With essentially a touch of magic, all the contents of MDF files, LDF files, snapshots, tempdb and backups are encrypted. Encryption occurs in real time, as data is written from memory to disk, and decryption occurs when data is read from disk and moved to memory. Encryption is done at the database level, so you can choose to encrypt as many databases as you want.
TDE is able to minimize resource utilization and hide its activities from user applications and the Relational Engine because all encryption / decryption occurs when data pages are moved between the buffer pool and disk. As SQL Server moves buffer pool data pages to either the MDF file, the LDF file, or tempdb, the data is encrypted in real time before being written to disk. In contrast, as data pages are moved from the MDF or tempdb file to the buffer pool, they are decrypted. In other words, when data is on disk, it is encrypted, but when it is in memory, it is not encrypted.
Regarding performance, the penalty for using Transparent Data Encryption, according to Microsoft, is between 3 to 5% performance drop only. A much lower value when compared to the performance penalty when using Always Encrypted, for example.
Encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key protected by a certificate stored in the server master database or an asymmetric key protected by an EKM module. TDE supports several different encryption options, such as AES with 128 bits, 192 bits or 256 bits or 3 Key Triple DES keys. You make your choice when implementing TDE.
Always Encrypted vs Transparent Data Encryption (TDE)
Below, I will list some similarities and differences between these two SQL Server encryption solutions:
| Always Encrypted | Transparent Data Encryption (TDE) |
|---|---|
| Column level | Database Level |
| Client Encryption (using a driver) | Server Encryption (Database Engine) |
| Server does not know encryption keys | Server knows encryption keys |
| Data in memory is encrypted | Data in memory is unprotected (plain-text) |
| Data on the network is encrypted | Data on the network is unprotected (plain-text) |
| Only users with key access can view the original data. Not even the DBA can view the original data without the key. | DBA can view original data without key |
| Backups and log files are encrypted | Backups and log files are encrypted |
| Requires application changes (may be small or large depending on the encryption algorithm you choose) | No app changes required |
| Available from SQL Server 2016 - All editions, up to Express (Express and Standard from 2016 SP1) | Available from SQL Server 2008 - Enterprise and Developer Only |
How to use Transparent Data Encryption (TDE)
To implement Transparent Data Encryption (TDE), we must follow the 4 steps below:
- Create a master key: A master key is created for the first time. This key, accessible with a password, is used to secure a certificate that will be created in the next step. This key is stored in the master database in an encrypted format.
- Create or obtain a master key protected certificate: This certificate is used to protect the database encryption key we will create in the next step. In addition, this certificate is protected by the master key we created in the previous step. The certificate is stored in the master database in an encrypted format.
- Create a database encryption key: This is the key that will be used by SQL Server to actually encrypt the data. It is protected by the certificate created in the previous step. This key is stored in the encrypted database and stored in an encrypted format.
- Enable TDE: After all of the above have been created, a command will be executed to tell SQL Server to begin encrypting all data using the database encryption key created in the previous step. This process may take some time, depending on the size of the database. Ideally, the database should not be used in production until the database has completed the initial encryption process.
The image below demonstrates well this TDE architecture:
To enable Transparent Data Encryption (TDE) on a database, you must use the following commands: (remember that tempdb is encrypted automatically when encryption is enabled on any other instance database)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE [master] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dirceuresende123_usa_uma_senha_forte_aqui_talquei'; GO CREATE CERTIFICATE MeuCertificadoDoServidor WITH SUBJECT = 'Meu Certificado da Instância'; GO USE [dirceuresende] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MeuCertificadoDoServidor; GO ALTER DATABASE dirceuresende SET ENCRYPTION ON; GO |
After executing these commands, your bank will be encrypted. To check which banks are encrypted or to track the progress of the encryption process, use the query below:
|
1 2 3 4 5 6 7 8 |
SELECT A.[name], A.is_master_key_encrypted_by_server, A.is_encrypted, B.* FROM sys.databases A JOIN sys.dm_database_encryption_keys B ON B.database_id = A.database_id |
Result:
Recalling that the percent_complete column indicates the progress of the database encryption process and the encryption_state column indicates what state the encryption is currently in, whose values in this column are:
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key Change in Progress
5 = Decryption in Progress
6 = Protection change in progress (the certificate or asymmetric key that is encrypting the database encryption key is changing)
After you complete encrypting your database, you will see a warning message informing you that you have to back up your certificate and private key IMMEDIATELY. If you lose your certificate or private key and need to restore this bank to another server, you will not be able to.
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
Command to back up certificate and private key:
|
1 2 3 4 5 6 7 8 |
USE [master] GO BACKUP CERTIFICATE MeuCertificadoDoServidor TO FILE = 'C:\Backups\Certificados\dirceuresende.cer' WITH PRIVATE KEY ( FILE = 'C:\Backups\Certificados\dirceuresende_Key.pvk', ENCRYPTION BY PASSWORD = 'dirceuresende123_usa_uma_senha_forte_aqui_talquei' ); GO |
Restoring a Bank with Transparent Data Encryption (TDE)
If you try to restore an encrypted backup file without restoring the master key first, you will encounter this error message:
Msg 33111, Level 16, State 3, Line 12
Cannot find server certificate with thumbprint '0xD98C862BF2A4B16D41DC8A96CBE819EFDCF33C00'.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.
Now I will show how to restore the database with TDE in another instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Cria uma nova master key.. Aqui você pode escolher uma nova senha CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'n6ApUO(i<8lRNT,2SF-{3LDzRUR;?MPd-Q-Fg3oec[wqMjKfp^'; GO -- Cria o certificado na master CREATE CERTIFICATE MeuCertificadoDoServidor FROM FILE = 'C:\Backups\Certificados\dirceuresende.cer' WITH PRIVATE KEY ( FILE = 'C:\Backups\Certificados\dirceuresende_Key.pvk', DECRYPTION BY PASSWORD = 'dirceuresende123_usa_uma_senha_forte_aqui_talquei' ) -- Restaura o banco RESTORE DATABASE dirceuresende FROM DISK = 'C:\Backups\dirceuresende.bak' WITH REPLACE, STATS = 5, MOVE 'dirceuresende_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\dirceuresende.mdf', MOVE 'dirceuresende_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\dirceuresende_log.ldf' |
If you are experiencing the error message below, you will probably need to correct the master key and private key permissions to enable inheritance:
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
To correct the inheritance of these 2 files, follow the steps below:
Transparent Data Encryption and transaction logs
Enabling a database to use TDE has the effect of zeroing the remaining portion of the virtual transaction log to enforce the next virtual transaction log. This ensures that no clear text is left in the transaction logs after the database is set for encryption.
All data written to the transaction log before a change to the database encryption key will be encrypted using the previous database encryption key.
After a database encryption key has been modified twice, a log backup must be performed so that the database encryption key can be modified again.
Transparent Data Encryption (TDE) and In-Memory OLTP
TDE can be enabled on a database that has OLTP In-Memory objects. In SQL Server 2016 and in the OLTP In-Memory log records, data is encrypted if TDE is enabled. In SQL Server 2014, OLTP In-Memory log records are encrypted if TDE is enabled, but files in the MEMORY_OPTIMIZED_DATA filegroup are not encrypted.
Transparent Data Encryption (TDE) limitations
- TDE does not protect data in memory, so sensitive data can be viewed by anyone who has DBO rights to a database or SA rights to the instance of SQL Server. In other words, TDE cannot prevent DBAs from viewing the data they want to see.
- TDE is not granular. The entire database is encrypted.
- TDE does not protect communications between client applications and SQL Server, so other encryption methods must be used to protect data that travels over the network and can be intercepted by malicious users.
- In TDE, all database files and filegroups are encrypted. If any filegroup in the database is marked as READ ONLY, the database encryption operation will fail.
- FILESTREAM data is not encrypted.
- If a database is being used for database mirror or log shipping, both databases will be encrypted. Log transactions will be encrypted when sent between them.
- When any database on an instance of SQL Server has TDE enabled, the tempdb database is automatically encrypted, which can contribute to poor performance of encrypted and unencrypted databases running on the same instance.
- Although fewer resources are required to implement TDE than column-level encryption, there will still be a little overhead, which can prevent it from being used on SQL Servers experiencing CPU bottleneck problems.
- Databases encrypted with TDE cannot take advantage of the new SQL Server 2008 backup compression. If you want to take advantage of backup compression and encryption, you will need to use a third-party application, such as SQL Backup, that allows you to perform these two tasks without penalty.
Dynamic Data Masking
After demonstrating the use of this encryption solution, you may be asking yourself, "What about Dynamic Data Masking (DDM) ???". Well, for a start, DDM is not a data encryption solution, but a data masking solution.
While data encryption effectively prevents your data from being restored (including from backups) and accessed improperly, data masking with Dynamic Data Masking only restricts content that is displayed at the end of a SELECT command, or that is, if you have access to a backup file, you can restore it to an instance where you are sysadmin and you have free access to previously masked data.
To learn more about Dynamic Data Masking, see the article. SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM), where I demonstrate how to use it, restrictions and even how to “break” the masking and access the original data (even without having permission to do so).
Well guys, I hope you enjoyed this article and now start protecting your data better!
Big hug and until next time!
1 Response
[…] Transparent Data Encryption (TDE) – | from SQL Server Microsoft Docs– SQL Server 2008 – How to Encrypt Your Data Using Transparent Data Encryption …– sys.dm_database_encryption_keys (Transact-SQL) – SQL Server | Microsoft […]