= 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: [[https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16|Enable Transparent Data Encryption (TDE)]]

{{{#!highlight sql
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ConstraintDB123!';
GO
CREATE CERTIFICATE MyDockerCert WITH SUBJECT = 'My DEK Certificate';
GO
USE UniversitySmall;
GO
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyDockerCert;
GO
/* 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. */

ALTER DATABASE UniversitySmall
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:

{{{#!highlight sql 
USE master;
GO
SELECT pvt_key_last_backup_date,
    Db_name(dek.database_id) AS encrypteddatabase,
    c.name AS Certificate_Name
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek
    ON c.thumbprint = dek.encryptor_thumbprint;
}}}

To backup the Certificate see: [[https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?view=sql-server-ver16|Backup Certificate]]. 

{{{#!highlight sql
BACKUP CERTIFICATE MyDockerCert TO FILE = 'MyDockerCert.pfx'
WITH 
PRIVATE KEY
(
	FILE = 'PrivateKey',
	ENCRYPTION BY PASSWORD = 'ConstraintDB123!'
)
}}}

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:

{{{#!highlight sql
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO
}}}

For information on the output see [[https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql?view=sql-server-ver16|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.