Edit

Extensible Key Management (EKM)

Applies to: SQL Server

SQL Server provides data encryption capabilities together with Extensible Key Management (EKM), using the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation. Encryption keys for data and key encryption are created in transient key containers, and you must export them from a provider before you store them in the database. This approach lets SQL Server handle key management, including an encryption key hierarchy and key backup.

With the growing demand for regulatory compliance and concern for data privacy, organizations are taking advantage of encryption as a way to provide a defense-in-depth solution. This approach is often impractical using only database encryption management tools. Hardware vendors provide products that address enterprise key management by using hardware security modules (HSMs). HSM devices store encryption keys on hardware or software modules. This approach is more secure because the encryption keys don't reside with the encrypted data.

Several vendors offer HSMs for both key management and encryption acceleration. HSM devices use hardware interfaces with a server process as an intermediary between an application and an HSM. Vendors also implement MSCAPI providers over their modules, which might be hardware or software. MSCAPI often offers only a subset of the functionality that an HSM offers. Vendors can also provide management software for HSMs, key configuration, and key access.

HSM implementations vary from vendor to vendor, and using them with SQL Server requires a common interface. Although MSCAPI provides this interface, it supports only a subset of the HSM features. It also has other limitations, such as the inability to natively persist symmetric keys, and a lack of session-oriented support.

SQL Server Extensible Key Management lets third-party EKM and HSM vendors register their modules in SQL Server. After a module is registered, SQL Server users can use the encryption keys stored on it. SQL Server can then access the advanced encryption features these modules support, such as bulk encryption and decryption, and key management functions such as key aging and key rotation.

When you run SQL Server in an Azure VM, SQL Server can use keys stored in Azure Key Vault. For more information, see Extensible Key Management using Azure Key Vault (SQL Server).

EKM configuration

Extensible Key Management isn't available in every edition of SQL Server. For a list of features supported by the editions in SQL Server, see Editions and supported features of SQL Server 2025.

By default, Extensible Key Management is off. To enable this feature, use the sp_configure (Transact-SQL) stored procedure with the following option and value:

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

Note

If you use sp_configure for this option on editions of SQL Server that don't support EKM, you receive an error.

To disable the feature, set the value to 0. For more information about how to set server options, see sp_configure (Transact-SQL).

How to use EKM

SQL Server Extensible Key Management lets you store the encryption keys that protect the database files in an off-box device such as a smart card, USB device, or EKM/HSM module. It also protects data from database administrators, except for members of the sysadmin fixed server role. You can encrypt data by using encryption keys that only the database user can access on the external EKM/HSM module.

Extensible Key Management also provides the following benefits:

  • An extra authorization check, which enables separation of duties.
  • Higher performance for hardware-based encryption and decryption.
  • External encryption key generation.
  • External encryption key storage, which physically separates data and keys.
  • Encryption key retrieval.
  • External encryption key retention, which enables encryption key rotation.
  • Easier encryption key recovery.
  • Manageable encryption key distribution.
  • Secure encryption key disposal.

You can use Extensible Key Management for a username and password combination or other methods defined by the EKM driver.

Caution

For troubleshooting, Microsoft technical support might require the encryption key from the EKM provider. You might also need to access vendor tools or processes to help resolve an issue.

Authentication with an EKM device

An EKM module can support more than one type of authentication. Each provider exposes only one type of authentication to SQL Server. That is, if the module supports both basic and other authentication types, it exposes one or the other, but not both.

EKM device-specific basic authentication by using a username and password

For EKM modules that support basic authentication by using a username and password pair, SQL Server provides transparent authentication by using credentials. For more information about credentials, see Credentials (Database Engine).

You can create a credential for an EKM provider and map it to a login, either a Windows or a SQL Server account, to access an EKM module on a per-login basis. The identity field of the credential contains the username, and the secret field contains a password to connect to an EKM module.

If no login-mapped credential exists for the EKM provider, SQL Server uses the credential mapped to the service account.

A login can have multiple credentials mapped to it, as long as each one is used for a distinct EKM provider. There must be only one mapped credential per EKM provider per login. You can map the same credential to other logins.

Other types of EKM device-specific authentication

For EKM modules that use authentication other than Windows or username and password combinations, you must perform authentication independently from SQL Server.

Encryption and decryption by an EKM device

Use the following functions and features to encrypt and decrypt data by using symmetric and asymmetric keys:

Function or feature Reference
Symmetric key encryption CREATE SYMMETRIC KEY (Transact-SQL)
Asymmetric key encryption CREATE ASYMMETRIC KEY (Transact-SQL)
EncryptByKey(key_guid, 'cleartext', ...) ENCRYPTBYKEY (Transact-SQL)
DecryptByKey(ciphertext, ...) DECRYPTBYKEY (Transact-SQL)
EncryptByAsmKey(key_guid, 'cleartext') ENCRYPTBYASYMKEY (Transact-SQL)
DecryptByAsmKey(ciphertext) DECRYPTBYASYMKEY (Transact-SQL)

Database key encryption by EKM keys

SQL Server can use EKM keys to encrypt other keys in a database. You can create and use both symmetric and asymmetric keys on an EKM device. You can encrypt native (non-EKM) symmetric keys with EKM asymmetric keys.

The following example creates a database symmetric key and encrypts it by using a key on an EKM module.

CREATE SYMMETRIC KEY Key1
WITH ALGORITHM = AES_256
ENCRYPTION BY EKM_AKey1;
GO

-- Open the database key.
OPEN SYMMETRIC KEY Key1
DECRYPTION BY EKM_AKey1;

For more information about database and server keys in SQL Server, see SQL Server and database encryption keys (Database Engine).

Note

You can't encrypt one EKM key with another EKM key.

SQL Server doesn't support signing modules with asymmetric keys generated from an EKM provider.

Related content

  • Last updated on