Encrypting an Entire Database using Transparent Database Encryption (TDE) on Sql Server

I needed to encrypt a database for a client. You can do this by following the information on the page below. In my case I am using a Docker instance to test this out... and of course it uses my super secret password (which you will recognize if you took my database class).

Information was learned starting here: Enable Transparent Data Encryption (TDE) 

   1 USE master;
   2 GO
   3 
   4 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ConstraintDB123!';
   5 GO
   6 CREATE CERTIFICATE MyDockerCert WITH SUBJECT = 'My DEK Certificate';
   7 GO
   8 USE UniversitySmall;
   9 GO
  10 CREATE DATABASE ENCRYPTION KEY 
  11 WITH ALGORITHM = AES_256
  12 ENCRYPTION BY SERVER CERTIFICATE MyDockerCert;
  13 GO
  14 /* Warning: The certificate used for encrypting the database encryption key has not been backed up. 
  15    You should immediately back up the certificate and the private key associated with the certificate. 
  16    If the certificate ever becomes unavailable or if you must restore or attach the database on another 
  17    server, you must have backups of both the certificate and the private key or you will not be able to 
  18    open the database. */
  19 
  20 ALTER DATABASE UniversitySmall
  21 SET ENCRYPTION ON;

The comment above is the output of creating the Database Encryption Key (DEK). Consequently, we need to backup the recommended keys and certificates. To identify keys that have not been backed up: 

   1 USE master;
   2 GO
   3 SELECT pvt_key_last_backup_date,
   4     Db_name(dek.database_id) AS encrypteddatabase,
   5     c.name AS Certificate_Name
   6 FROM sys.certificates c
   7 INNER JOIN sys.dm_database_encryption_keys dek
   8     ON c.thumbprint = dek.encryptor_thumbprint;

To backup the Certificate see: Backup Certificate. 

   1 BACKUP CERTIFICATE MyDockerCert TO FILE = 'MyDockerCert.pfx'
   2 WITH 
   3 PRIVATE KEY
   4 (
   5         FILE = 'PrivateKey',
   6         ENCRYPTION BY PASSWORD = 'ConstraintDB123!'
   7 )

By default these are stored in the DATA folder of the SQL Server. On Docker this is in /var/opt/mssql/data. If you want to you can specify the place for the files to be stored using either a local file or a UNC path.

Once you have downloaded them to a secure location, you should delete the Certificate and Private Key files from the data directory.

To see the encryption state: 

   1 SELECT
   2     db.name,
   3     db.is_encrypted,
   4     dm.encryption_state,
   5     dm.percent_complete,
   6     dm.key_algorithm,
   7     dm.key_length
   8 FROM
   9     sys.databases db
  10     LEFT OUTER JOIN sys.dm_database_encryption_keys dm
  11         ON db.database_id = dm.database_id;
  12 GO

For information on the output see here. IMPORTANT: percent_complete is not how much of the database has been encrypted, but rather the percent complete of the database encryption state change AND WILL BE 0 if there is not state change. If the database was in the process of being encrypted, the encryption_state = 2.

Note: FILESTREAM data isn't encrypted, even when you enable TDE.

DatabaseManagementSystems/SqlServerEncryptingDatabases (last edited 2024-02-27 01:08:49 by scot)