Configuring Microsoft SQL Server Database Encryption with TDE Key

This section explains how to configure Microsoft SQL Server database encryption with TDE key.

Database Encryption with TDE key

To create the database encryption with TDE key.

1. Use the TDE_KEY to create a database encryption key.
   
   In this example, we used a test database “testdb” and "TDE_KEY" to create database encryption key.
   

   Copy

   use testdb
   GO
   CREATE DATABASE ENCRYPTION KEY  
   WITH ALGORITHM  = AES_256  
   ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_KEY;
   GO
   
   ALTER DATABASE testdb 
   SET ENCRYPTION ON ;
   GO

   
   

2. Check created encryption keys and progress of encryption. There are fields like “encryption_state” and “percent_complete” which show relevant information. Also check the “encryptor thumbprint” to know the version of key used to encrypt the database key.

   Note: The key key thumbprint in SQL server is same as the Key Id in KeyControl. To find the Key ID,

   

   You can list all the asymmetric keys and database encryption keys using following SQL statements.

   Copy

   use master
   GO
   Select * from sys.dm_database_encryption_keys
   Select * from sys.asymmetric_keys
   GO

For more information regarding these options and fields, see https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql?view=sql-server-ver15.

For more information on how to monitor and manage Transparent Data Encryption (TDE) in SQL Server, see https://www.sqlshack.com/how-to-monitor-and-manage-transparent-data-encryption-tde-in-sql-server/.

Switching on encryption using SQL Server Management Studio

Only one TDEDEK per database can be used at a time.

To create the TDEDEK using the `dbAsymWrappingKey` (TDEKEK) created above for database encryption, and enable TDE on the database (`TestDatabase`):

1. In SQL Server Management Studio, navigate to Databases > TestDatabase.

2. Right-click `TestDatabase`, then select Tasks > Manage Database Encryption....

3. Set Encryption Algorithm to the desired algorithm.

4. Ensure that Use server asymmetric key is selected, then select `dbAsymWrappingKey` from the drop down list.

5. Ensure Set Database Encryption On is selected, then click OK.

After successfully setting up the TDE encryption, the person performing the set up no longer needs to use the same protection domain for their login credential as used for the `tdeCredential`.

Verifying by inspection that TDE has occurred on disk

Note that the inspection method will only work for data that can be backed up in the database (on disk) as human-readable character strings.

To check the encryption state of the database, refer to How to check the TDE encryption/decryption state of a database .

If the TDE has been successful, then an 'Encrypted' state should be indicated.

Querying the `TestTable` or database contents will not indicate whether the table was encrypted on disk, because it will be automatically decrypted when loaded into memory.

TDE encryption on disk can be verified by inspecting backup copies of the `TestDatabase` from before and after the TDE encryption.

After TDE encryption has been set up and checked to be functioning, make a backup copy of the encrypted `TestDatabase`. For more detail, see Database Backup and Restore .

You should now have the following unencrypted and encrypted backup copies of the `TestDatabase`:

• `<Drive>:\<Backup_directory_path>\TestDatabase_TDE_Unencrypted.bak`

• `<Drive>:\<Backup_directory_path>\TestDatabase_TDE_Encrypted.bak`

These backup files can be inspected using a text editor, provided you have appropriate access permissions.

1. Open `TestDatabase_TDE_Unencrypted.bak` in a text editor and search for a known value. It should be possible to find the plaintext `FirstName` or else `LastName` of anyone mentioned in the original and unencrypted `TestTable`.

2. Open `TestDatabase_TDE_Encrypted.bak` in a text editor and search for the same value. It should not be possible to find any plaintext names or other values in the encrypted file.

The backup files circumvent the automatic TDE decryption of the database, allowing direct inspection of the contents as stored on disk. Although this inspection has been carried out on backup files, these should contain information similar enough to the actual database disk contents to demonstrate whether the TDE encryption is working on disk or not.

To replace the TDE Key (TDEKEK)

1. Follow the procedure Configuring Microsoft SQL Server Database Encryption with TDE Key to create a new asymmetric TDEKEK called `dbAnotherAsymWrappingKey`.

2. Create a new credential called `anotherTdeCredential`.

3. Create a new TDE login called `anotherTdeLogin`. Map it to `dbAnotherAsymWrappingKey` and the new `anotherTdeCredential`.

4. In SQL Server Management Studio, navigate to Databases > TestDatabase.

5. Right-click TestDatabase, then select Tasks > Manage Database Encryption....

6. Select Re-Encrypt Database Encryption Key and Use server asymmetric. Select `dbAnotherAsymWrappingKey` from the drop down list.

7. Ensure Regenerate Database Encryption Key is not selected.

8. Ensure Set Database Encryption On is selected, then click OK.

To replace the TDE Key (TDEDEK)

1. In SQL Server Management Studio, navigate to Databases > TestDatabase.

2. Right-click TestDatabase, then select Tasks > Manage Database Encryption....

3. Ensure Re-Encrypt Database Encryption Key is not selected.

4. Ensure Regenerate Database Encryption Key is selected, then select AES 256 from the drop down list.

5. Ensure Set Database Encryption On is selected, then click OK.

How to check the TDE encryption/decryption state of a database

Note: The following `encryption_state` information applies to TDE encryption only.

You can use the following T-SQL queries to find the current encryption state of a database. This can be particularly useful where large amounts of data have to be processed and you wish to check progress before attempting any further operations on the database.

First, find the database ID from the database name by using the following query:

SELECT DB_ID('<Database name>') AS [Database ID]; 
GO 
SELECT * FROM sys.dm_database_encryption_keys
GO

Where <Database name> is the name of the database you are interested in.

The above query provides a table output that includes columns titled `database_id` and `encryption_state`. Find the database ID you are interested in and look at the corresponding value for the encryption state.

Alternatively, you can use the composite query:

SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys
GO

Where <database_id> is the ID number of the database you are interested in.

Values of `encryption_state` are as follows: