Cryptographic Security Platform 1.6 - Vault 10.6.1 administration and user guide [PDF][Releases]

These examples assume that both the 'TestDatabase' and 'TestTable' as described in T-SQL Shortcuts and Tips have been created, and are not currently encrypted.

When TDE encryption has been correctly set up and switched on, the database it is protecting will appear as normal to any user who has been granted suitable permissions to use the database. The user does not require a Cryptographic Security Platform Vault SQLEKM provider credential to access or modify TDE protected data.

Note that the person setting up or managing the TDE encryption keys must use the same protection domain for their login credential as used for the 'tdeCredential' below.

The TDE Database Encryption Key (TDEDEK) is a symmetric key that is used to perform the actual encryption of the database. It is created by SQL Server and cannot be exported from the database meaning that it cannot be created or directly protected by the Cryptographic Security Platform Vault SQLEKM provider. In order to protect the TDEDEK within the database it may in turn be encrypted by a wrapping key. The wrapping key is called the TDE Key Encryption Key (TDEKEK). In this case, the Cryptographic Security Platform Vault SQLEKM provider can create and protect the TDEKEK.

Before running the following examples, you should create a backup copy of the unencrypted database. See Restoring the TDE Encrypted Database . Alternatively, you may prefer to adapt the T-SQL query shown in Making a database backup . Save the backup as '<Drive>:\<Backup_directory_path>\TestDatabase_TDE_Unencrypted.bak`.

Note: If you are using a shared disk cluster, TDE should be configured on the active node.

Note: In this document TDE key refers to TDE Key Encryption Key(TDEKEK)

There are two ways to create TDE keys:

Create key in Cryptographic Security Platform Vault and open in MS SQL Server
Create key directly in Microsoft SQL Server using SQL script

Create TDE Key in Cryptographic Security Platform Vault

Navigate to CLOUD KEYS > CloudKeys and select the Key Set that you created.
Select Actions > Create CloudKey.
On the Details tab of the Create CloudKey dialog box, enter the following:
Field
Description
Name
Enter the name for the CloudKey. For this example, use master_key1.
Description
Enter the optional description for the CloudKey.
Cipher
Select RSA-2018.
Click Continue.
On the Schedule tab, determine the rotation schedule for the CloudKey. This can be one of the following:
- Inherit from Key Set—The CloudKey will use the default schedule from the Key Set. If the Key Set schedule changes after the CloudKey is created, the CloudKey schedule will not be updated.
- Never—The CloudKey will never be rotated.
- Once a year—The CloudKey will be rotated once a year.
- Every 6 months—The CloudKey will be rotated once every 6 months.
- Every 30 days—The CloudKey will be rotated once every 30 days.
- Other—The CloudKey will be rotated at the interval you select.
Choose when this version of the CloudKey should expire. The per version expiration can be one of the following:
- Never—The CloudKey version will never expire.
- Fixed Date—All CloudKey versions will expire on the date that you set.
- Relative Expiry—Each CloudKey version will expire after the number of days that you set.
Choose when the CloudKey as a whole should expire. This can be Never, or you can choose a specific date.
If you selected an expiration date, choose the Expire Action to define what happens to the CloudKey when it expires. This can be one of the following:
- Disable—The key will remain in the cloud, but is disabled and cannot be used by any applications.
- Delete—The key is disabled in the cloud and cannot be used by any applications. You can set the date when the key is permanently deleted.
Note: When the CloudKey expires, the selected Expire Action is performed on the key. The Cryptographic Security Platform Vault handles the expiry date and expire action. The expire date is not set in the cloud service provider.
Click Apply.
Navigate to the details page of the CloudKey, and select the Key ID for the keyset.
To configure the TDE key in Microsoft SQL Server, create an asymmetric key stored inside the EKM provider. Login again on the SQL Server and create a master key (KEK) using the name of the key generated in the previous step and the provider created earlier (see Configuring Microsoft SQL Server for EKM ).
```
USE master; 
CREATE ASYMMETRIC KEY TDE_KEY        -- Give the KEK a name in SQL Server
FROM PROVIDER EKM_Prov WITH 
PROVIDER_KEY_NAME = '<Cloud Key>',   -- master_key1 created in last Step 
CREATION_DISPOSITION = OPEN_EXISTING;
GO
```

Creating TDE key using Microsoft SQL Server

To create a TDE Key programmatically using SQL script directly in MS SQL Server:

USE master 
CREATE ASYMMETRIC KEY dbAsymWrappingKey 
FROM PROVIDER <provider name> 
WITH PROVIDER_KEY_NAME='ekmAsymWrappingKey', 
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM = RSA_2048;
GO

Where <provider name> is the name that is used to refer to the Cryptographic Security Platform Vault SQLEKM provider. The TDE Key is the only key you must create in the 'master' database.

Note: The key thumbprint in SQL Server is same as the keyid in Cryptographic Security Platform Vault.

Check the key is created and has correct key thumbprint.

Copy

USE master; 
SELECT name,thumbprint,algorithm_desc FROM sys.asymmetric_keys 
GO

To check the TDEKEK, in SQL Server Management Studio navigate to Databases > System Databases > Master > Security > Asymmetric Keys. If necessary, right-click and select Refresh.

Create credential and login for TDE

Create another credential and a login for TDE. Add the credential to the login.

Setting up TDE login programmatically