Configure TDE

The TDE Database Encryption Key (TDEDEK) is a symmetric key that is used to perform the actual encryption of the database and are unique to a given database. It is created by SQL Server and cannot be exported from the database, meaning it cannot be created or directly protected by the SQLEKM provider (nShield HSM).

The TDEDEK is protected within the database by encrypting it with a wrapping key. The wrapping key is called the TDE Key Encryption Key (TDEKEK). The TDEKEK is an asymmetric key protected by the SQLEKM provider in the nShield HSM. It is possible to have a single TDEKEK for multiple databases, or different TDEKEKs for different databases.

The TDEKEK must be created under the tdeLogin/tdeCredential. However, the current user does not have to use the tdeCredential, so long as the user credential is using the same OCS or Softcard as the tdeCredential.

  1. Create a TDEKEK

  2. Create a TDE login and credential

  3. Create the TDEDEK and switch on encryption

  4. Key rotation - Replace the TDEKEK

  5. Key rotation - Replace the TDEDEK

Create a TDEKEK

To create a TDEKEK in the master database:

  1. Insert the OCS in the HSM slot or TVD. If using Softcard protection, no action is needed.

  2. Run the following query:

    USE master;
CREATE ASYMMETRIC KEY "<name_of_key_in_database>"
FROM PROVIDER "<SQLEKM_provider>"
WITH
PROVIDER_KEY_NAME = '<name_of_key_in_SQLEKM_provider>',
CREATION_DISPOSITION = CREATE_NEW,
ALGORITHM = <asymmetric_algorithm_desc>;
GO

    Where:

    name_of_key_in_database

    The name given to the key in the database.
    name_of_key_in_SQLEKM_provider

    The name given to the key in the SQLEKM provider.
    asymmetric_algorithm_desc>

    A valid asymmetric key algorithm descriptor.

    See Verify the SQLEKM provider configuration.

    For example:

    USE master;
CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM',
CREATION_DISPOSITION = CREATE_NEW,
ALGORITHM = RSA_2048;
GO

    Notice the newly created key highlighted in the object explorer.

    Create Key TDEKEK 1

  3. The key generated can also be verified using a CLI command:

    > nfkminfo -l

Keys protected by cardsets:
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecaaf2c3e8cb8f0dd3756678b757468a4de120c4 `AsymTestWrappingKeySQLEKM'

    The rocs utility shows the names and protection methods of the keys.

    > rocs
`rocs' key recovery tool
Useful commands: `help', `help intro', `quit'.
rocs> list keys
  No. Name                     App        Protected by
    1 AsymTestWrappingKeySQLEK simple     testOCS
rocs> exit

Create a TDE login and credential

A tdeLogin and tdeCredential allows an ordinary database user, who is fully authorized to use the database, but has no SQLEKM credentials of their own, to perform query operations using a TDE encrypted database. Without the tdeLogin and tdeCredential, then every user would need their own credentials. It is beyond the scope of this document to provide an example of how to use these credentials, only on how to create them.

Create a TDE credential

To create a TDE credential:

  1. In SQL Server Management Studio, navigate to Security > Credentials.

  2. Right-click Credentials, then select New Credential.

  3. Under New Credential:

    1. Enter the Credential name.

    2. For Identity, enter the OCS card name.

    3. Enter a Password, and confirm the password.

    4. Select Use Encryption Provider.

    5. For Provider, select nDSOP.

    6. Select OK.

      TDE 1 Credential

  4. Notice the credential created.

    TDE 2 Credential Created

Create a TDE login

To create a TDE login:

  1. In SQL Server Management Studio, navigate to Security > Logins.

  2. Right-click Logins, then select New Login.

  3. Enter the Login name.

  4. Select Mapped to asymmetric key. Then select the asymmetric key created earlier.

  5. Select Map to Credential. Then select the TDE credential created earlier. Then select Add.

  6. Select OK.

    TDE 3 Login

  7. Notice the login created.

    TDE 4 Login Created

Create the TDEDEK and switch on encryption

To create the TDEDEK and switch on encryption:

  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 AES 256 or your choice.

  4. Select Use server asymmetric key. Then select the asymmetric key created earlier.

  5. Select Set Database Encryption On. Then select OK. Restart the Microsoft SQL Server Management Studio and repeat these steps if it fails.

    TDE 5 Create TDEDEK

  6. Run the following query to verify the encryption state:

    /****** Script for SelectTopNRows command from SSMS  ******/
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.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
    TDE 6 Verify Encryption State

    The following table shows the value returned for encryption state and the meaning.

    Encryption state Meaning

    0

    Encryption disabled (or no encryption key)

    1

    Unencrypted or Decrypted

    2

    Unencrypted or Decrypted

    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 being changed)

  7. Turn off encryption of by clearing Set Database Encryption On in the steps above.

Key rotation - Replace the TDEKEK

This is the wrapping key called TDE Key Encryption Key, an asymmetric key protected by the SQLEKM provider in the nShield HSM.

  1. Create a new asymmetric TDEKEK. Follow the procedure in Create a TDEKEK. For example:

    USE master;
CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase2"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM2',
CREATION_DISPOSITION = CREATE_NEW,
ALGORITHM = RSA_2048;
GO
    > nfkminfo -l

Keys protected by cardsets:
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-ecaaf2c3e8cb8f0dd3756678b757468a4de120c4 `AsymTestWrappingKeySQLEKM'
 key_simple_sqlekm-edb3d45a28e5a6b22b033684ce589d9e198272c2-f110419800476ccf0bd04b3cd531a59ce3cd2af6 `AsymTestWrappingKeySQLEKM2'
    Create Key TDEKEK 2

  2. Create a new TDE credential. Follow the procedure in Create a TDE credential.

  3. Create a new TDE login. Follow the procedure in Create a TDE login.

    Create Credential Login 2

  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.

  7. Select the newly created asymmetric key AsymTestWrappingKeyDatabase2.

  8. Deselect Regenerate Database Encryption Key.

  9. Select Set Database Encryption On.

  10. Select OK.

    Replace TDEKEK 1

  11. Verify the encryption state as shown in Create the TDEDEK and switch on encryption.

Key rotation - Replace the TDEDEK

This is the key called TDE Database Encryption Key, a symmetric used to perform the actual encryption of the database. It is created by SQL Server and cannot be exported from the database. It is protected within the database by encrypting it with a wrapping key TDEKEK.

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

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

  3. Deselect Re-Encrypt Database Encryption Key.

  4. Select Regenerate Database Encryption Key.

  5. Select AES 256.

  6. Select Set Database Encryption On.

  7. Select OK.

    Replace TDEDEK 1

  8. Verify the encryption state as shown in Create the TDEDEK and switch on encryption.