28 July,2020 by Jack Vamvas
In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection.
Setting up TDE in SQL Server is in summary
Create a master key.
Create or obtain a certificate protected by the master key.
Create a database encryption key and protect it by using the certificate.
Set the database to use encryption
If managing a large amount of databases - especially if fulfilling daily database backups & restores or other database refreshes , key management is critical to the smooth operation. Develop a process to make keys available for restores.
This SQL Code is a used to create certificates and apply TDE to SQL Server databases.
use master go CREATE DATABASE myDB; USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'a_strong_password' go CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate' go -- export the backup certificate to a file BACKUP CERTIFICATE MyServerCert TO FILE = 'h:\MyServerCert.cert' WITH PRIVATE KEY ( FILE = 'h:\MyServerCert.key', ENCRYPTION BY PASSWORD = 'My!!Strong123478Cert') GO USE myDB GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert GO ALTER DATABASE myDB SET ENCRYPTION ON GO
SQL Code to monitor TDE
SELECT [name] , [principal_id] , [algorithm_desc] , [create_date] from master.sys.symmetric_keys SELECT name AS certificate_name, pvt_key_encryption_type_desc AS pvt_key_encryption, thumbprint FROM master.sys.certificates SELECT DB_NAME(database_id) AS database_name, key_algorithm, key_length, encryptor_type, encryptor_thumbprint FROM sys.dm_database_encryption_keys SELECT DB_NAME (e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys AS e LEFT JOIN master.sys.asymmetric_keys AS c ON e.encryptor_thumbprint = c.thumbprint SELECT [name], is_master_key_encrypted_by_server, is_encrypted FROM master.sys.databases
A common question when designing a TDE enabled database is the performance impact. Read How to measure Transparent Data Encryption (TDE) Performance
Read more on database encryption
Encrypting data in SQL Server - SQL Server DBA
!
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |