Edit

Share via

Enable transparent data encryption on SQL Server Using EKM

Applies to: SQL Server

This article describes how to enable transparent data encryption (TDE) in SQL Server to protect a database encryption key by using an asymmetric key stored in an extensible key management (EKM) module with Transact-SQL.

TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. The database encryption key can also be protected using a certificate, which is protected by the database master key (DMK) of the master database. For more information about protecting the database encryption key by using the DMK, see Transparent data encryption (TDE). For information about configuring TDE when SQL Server is running on an Azure Virtual Machine, see Extensible Key Management Using Azure Key Vault (SQL Server). For information about configuring TDE using a key in the Azure key vault, see Use SQL Server Connector with SQL Encryption Features.

Limitations

You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. The EKM module must be able to authenticate that user.

At startup, the Database Engine must open the database. You should create a credential that will be authenticated by the EKM, and add it to a login that is based on an asymmetric key. Users can't sign in using that login, but the Database Engine can authenticate itself with the EKM device.

If the asymmetric key stored in the EKM module is lost, the database can't be opened by SQL Server. If the EKM provider lets you back up the asymmetric key, you should create a backup and store it in a secure ___location.

The options and parameters required by your EKM provider can differ from what is provided in the following code example. For more information, see your EKM provider.

Permissions

This article 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.

Use Transact-SQL

  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_Files folder.

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

    Note

    SQL Server doesn't supply an EKM provider. Each EKM provider can have different procedures for installing, configuring, and authorizing users. To complete this step, consult your EKM provider documentation.

  3. In Object Explorer, connect to an instance of Database Engine.

  4. On the Standard bar, select New Query.

  5. Copy and paste the following example into the query window and select Execute.

    -- Enable advanced options.
EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

-- Enable EKM provider
EXECUTE sp_configure 'EKM provider enabled', 1;
GO

RECONFIGURE;
GO

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

-- Create a credential that will be used by system administrators.
CREATE CREDENTIAL sa_ekm_tde_cred
    WITH IDENTITY = 'Identity1',
    SECRET = '<password>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;
GO

-- Add the credential to a high privileged user such as your
-- own ___domain login in the format [DOMAIN\login].
ALTER LOGIN [Contoso\Mary] ADD CREDENTIAL sa_ekm_tde_cred;
GO

-- create an asymmetric key stored inside the 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

-- Create a credential that will be used by the Database Engine.
CREATE CREDENTIAL ekm_tde_cred
    WITH IDENTITY = 'Identity2', SECRET = '<secret>'
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;

-- 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 that will be used for TDE.
USE AdventureWorks2022;
GO

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

-- Alter the database to enable transparent data encryption.
ALTER DATABASE AdventureWorks2022
    SET ENCRYPTION ON;
GO

Related content