Transparent Data Encryption (TDE)

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 KeyControl 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 KeyControl 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 KeyControl 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 Backup and Restore 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)

Create TDE Key

There are two ways to create TDE keys:

• Create key in KeyControl and open in MS SQL Server

• Create key directly in Microsoft SQL Server using SQL script

Create TDE Key in KeyControl

1. Navigate to the CLOUD KEYS > CloudKeys tab. Create the Key which will be opened in SQL server.

   

2. From the Action menu, select Create CloudKey.

   

   

   

3. 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 master key (KEK) using the name of the key generated in the previous step and the provider created earlier (see, Configure Cryptographic Provider on SQL Server ).

   Copy

   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 KeyControl 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 KeyControl.

Check the key is created and has correct key thumbprint.

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

CREATE CREDENTIAL tde_ekm_cred 
WITH IDENTITY = 'tde_connect1', 
SECRET = 'xdZ5TCNRLcldfeCr8SDZyu+TJF1kH5MGMZgaj2vZeUgP3Nf2xBh/lWicDzhvaWZCN3w3A5gVlkgG05AnfdlIYafP6HE+z SqsGXAy4Fx7deZYqTbOcz3HOYmu99IE81fATfG58C1qukmzY9UW3z04p3TaAM2xho30SIhm2hs1iupsXADGBuSVYSxi5C8 aH26jrXZFl6hIiInwK8F3GF3MQSWksRmgruAOSBLfnh+iasM1vqkZ/BHZ/3vRcsZZTrC3DycHXGrZqwcVxZaPsQlok2vC0 cRw6m7afBCJI76y854kIGEflOCvQugGxxyEBpPOOuLrCEQo0y5jr9x2dYgWIr4X24qTnW9+BVMlMqGh2oOmqBqqNFBVUBE UjSIjHZuE/4xo/lH6aknRu0PHP2JDhvDGpouRfCaJ7/5q1dygiEl1OcSoANemqu44GUaVyAS1esIM182T6rNbbmRgRJsyq znrdTLx02oUPbtbUXRUdh7hgYGPBB+uvid2YYT+NOsXM2nR0XdkxJ59x2RbFErjTGuf6mj756qE2RBzV9eeIEjMQgiajfT UNWz/NTk+Ca5z6Rdznig8VTCLR0Q3iHxF6zb5jSYkLeo0pbeUuuSLtlPX7kkrzFt07j2dj8q3V2lkcvYLOMdgtzzW/ILGj S0jEyZBbyROxK+48WSQ244EVhs=' 
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; 
GO 

CREATE LOGIN TDE_Login 
FROM ASYMMETRIC KEY TDE_KEY ; 
GO 

ALTER LOGIN TDE_Login 
ADD CREDENTIAL tde_ekm_cred 
GO

Setting up TDE login and credential in SQL Server Management Studio

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

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

3. Set Credential name to tdeCredential (for example).

4. Set Identity to <protection domain id>, where <protection domain id> matches the id of the protection domain. This must be the same key protector as that used to protect the `ekmAsymWrappingKey` created above.

5. Set Password to <passphrase>, where <passphrase> matches the passphrase of the protection domain.

6. Set se Encryption Provider to <provider name>, where <provider name> is the name of the provider you are using. Click OK.

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

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

9. Set Login name to tdeLogin (for example).

    

   Check credential is created correctly

1. Ensure Mapped to asymmetric key is selected, then select `dbAsymWrappingKey` (the TDEKEK created previously) from the drop down list.

2. Ensure Map to Credential is selected, then select tdeCredential from the drop down list. Click dd, then click OK.

3. In SQL Server Management Studio, check that the tdeCredential exists by navigating to Security > Credentials. If necessary, right-click and select Refresh. You should see the credential name listed.

4. In SQL Server Management Studio, check that the tdeLogin exists by navigating to Security > Logins. If necessary, right-click and select Refresh. You should see the login name listed.