• Article

How to: Enable TDE Using EKM

Transparent data encryption (TDE) encrypts the storage of an entire database. TDE encrypts the data using a symmetric key called the database encryption key. This topic describes how to protect the database encryption key by using an asymmetric key stored in an extensible key management (EKM) module.

The database encryption key can also be protected using a certificate which is protected by the database master key of the master database. For more information about protecting the database encryption key by using the database master key, see Understanding Transparent Data Encryption (TDE).

The basic steps to enable TDE using an EKM are:

  1. Install an EKM provider. The procedures for this step will vary depending on the requirements of the EKM provider, but might include installing a certificate from the EKM provider in the local certificate store, and copying EKM DLL files onto the SQL Server computer.

  2. Configure SQL Server to use the EKM provider.

  3. If the EKM module uses basic authentication, create a credential and add the credential to a user.

  4. Create an asymmetric key protected by the EKM provider.

  5. If the EKM module uses basic authentication, create a credential protected by the asymmetric key for use by the Database Engine.

  6. Create a symmetric database encryption key.

  7. Enable TDE using the database encryption key.

To complete the steps of this topic, connect to the SQL Server using a Query Editor window in SQL Server Management Studio.

To install and configure the cryptographic provider

  1. Copy the files supplied by the EKM provider to an appropriate location on the SQL Server computer. In this example, we use the C:\EKM folder.

  2. Install certificates to the computer as required by your EKM provider.

    Note

    SQL Server does not supply an EKM provider. Each EKM provider can have different procedures for installing, configuring and authorizing users. Consult your EKM provider documentation to complete this step.

To configure SQL Server to use an EKM provider and create the provider in SQL Server

  1. Enabling an EKM provider is an advanced option. First, execute the following statements to show the advanced options.

    sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO

  2. Execute the following statement to enable EKM providers:

    sp_configure 'EKM provider enabled', 1 ;
GO
RECONFIGURE ;
GO

  3. Execute the following statement to create a cryptographic provider, which we have chosen to call EKM_Prov, based on an EKM provider:

    CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov 
FROM FILE = 'C:\EKM_Files\KeyProvFile.dll' ;
GO

    Note

    The EKM DLL must be digitally signed.

Create a Credential Used to Encrypt the Database

You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. That user must be able to be authenticated by the EKM module. The following procedure creates a credential that uses the EKM identity, and adds the credential to a high privileged user. The following procedures assume that the EKM module can authenticate a user named 'Identity1'. A password should be provided instead of the asterisks that are shown here. These steps are required when the EKM provider uses basic authentication, but the steps in this section can be skipped when the EKM module uses other authentication.

To create a credential and add them to the login of a high privileged user

  1. Execute the following statement to create a credential that will be used by system administrators:

    CREATE CREDENTIAL sa_ekm_tde_cred 
WITH IDENTITY = 'Identity1', 
SECRET = '*************' 
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO

  2. Execute the following statement to add the system administrator credential to a high privileged user such as your own domain login in the format [DOMAIN\login]:

    ALTER LOGIN [DOMAIN\login]
ADD CREDENTIAL sa_ekm_tde_cred ;
GO

Create an Asymmetric Key

Create an asymmetric key to protect access to the database encryption key. This key is used by SQL Server to access the database encryption key.

To create a database encryption key

  • Execute the following statement to create an asymmetric key stored inside the EKM provider. In this example, SQL Server will identify the key by the name ekm_login_key. Inside the EKM device, the key will have the name SQL_Server_Key.

    Note

    The options and parameters required by your EKM provider can differ from this statement. For more information, see your EKM provider.

    USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key 
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_512,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO

    Note

    If the asymmetric key stored in the EKM module is lost, the database will not be able to be opened by SQL Server. If the EKM provider lets you back up the asymmetric key, you should create a back up and store it in a secure location.

Create a Credential for use by the Database Engine

Upon start up the Database Engine must open the database. The following procedure creates a credential with to be authenticated by the EKM, and adds that to a login that is based on an asymmetric key. Users cannot login using that login, but the Database Engine will be able to authenticate itself with the EKM device. The following procedures assume that the EKM module can authenticate a user named 'Identity2'. A password should be provided instead of the asterisks below. These steps are required when the EKM provider uses basic authentication, but the steps in this section can be skipped when the EKM module uses other authentication.

To create credentials and add them to logins

  1. Execute the following statement to create a credential that will be used by the Database Engine:

    CREATE CREDENTIAL ekm_tde_cred 
WITH IDENTITY = 'Identity2' 
, SECRET = '*************' 
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;

  2. Execute the following statement to add a login used by TDE, and add the new credential to the login:

    CREATE LOGIN EKM_Login 
FROM ASYMMETRIC KEY ekm_login_key ;
GO

ALTER LOGIN EKM_Login 
ADD CREDENTIAL ekm_tde_cred ;
GO

Create the Database Encryption Key

The database encryption key is a symmetric key. Create the database encryption key in the master database.

To create a database encryption key

  1. Change to the database that will be encrypted:

    USE AdventureWorks ;
GO

  2. Execute the following code to create the database encryption key that will be used for TDE:

    CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_128
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO

  3. Execute the following code to alter the database to enable transparent data encryption:

    ALTER DATABASE AdventureWorks 
SET ENCRYPTION ON ;
GO

    The database is now stored in an encrypted format.

Security

This topic uses the following permissions:

  • To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

  • Requires ALTER ANY CREDENTIAL permission.

  • Requires ALTER ANY LOGIN permission.

  • Requires CREATE ASYMMETRIC KEY permission.

  • Requires CONTROL permission on the database to encrypt the database.