Upravit

Sdílet prostřednictvím

Extensible Key Management (EKM)

  • Článek

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 they must be exported from a provider before they are stored in the database. This approach enables key management that includes an encryption key hierarchy and key backup, to be handled by SQL Server.

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 (HSM). HSM devices store encryption keys on hardware or software modules. This is a more secure solution because the encryption keys do not reside with encryption data.

A number of vendors offer HSM 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 is offered by an HSM. Vendors can also provide management software for HSM, key configuration, and key access.

HSM implementations vary from vendor to vendor, and to use them with SQL Server requires a common interface. Although the 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.

The SQL Server Extensible Key Management enables third-party EKM/HSM vendors to register their modules in SQL Server. When registered, SQL Server users can use the encryption keys stored on EKM modules. This enables SQL Server to 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 running SQL Server in an Azure VM, SQL Server can use keys stored in the Azure Key Vault. For more information, see Extensible Key Management Using Azure Key Vault (SQL Server).

EKM Configuration

Extensible Key Management is not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2016.

By default, Extensible Key Management is off. To enable this feature, use the sp_configure command that has the following option and value, as in the following example:

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

Note

If you use the sp_configure command for this option on editions of SQL Server that do not support EKM, you will 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 enables the encryption keys that protect the database files to be stored in an off-box device such as a smartcard, USB device, or EKM/HSM module. This also enables data protection from database administrators (except members of the sysadmin group). Data can be encrypted by using encryption keys that only the database user has access to on the external EKM/HSM module.

Extensible Key Management also provides the following benefits:

  • Additional authorization check (enabling separation of duties).

  • Higher performance for hardware-based encryption/decryption.

  • External encryption key generation.

  • External encryption key storage (physical separation of data and keys).

  • Encryption key retrieval.

  • External encryption key retention (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 Basic or Other authentication types, it exposes one or the other, but not both.

EKM Device-Specific Basic Authentication Using username/password

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

A credential can be created for an EKM provider and mapped to a login (both Windows and SQL Server accounts) to access an EKM module on per-login basis. The identity field of the credential contains the username; the secret field contains a password to connect to an EKM module.

If there is no login mapped credential for the EKM provider, the credential mapped to the SQL Server service account is used.

A login can have multiple credentials mapped to it, as long as they are used for distinctive EKM providers. There must be only one mapped credential per EKM provider per login. The same credential can be mapped to other logins.

Other Types of EKM Device-Specific Authentication

For EKM modules that have authentication other than Windows or user/password combinations, authentication must be performed independently from SQL Server.

Encryption and Decryption by an EKM Device

You can 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 Keys 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 using a key on an EKM module.

CREATE SYMMETRIC KEY Key1  
WITH ALGORITHM = AES_256  
ENCRYPTION BY EKM_AKey1;  
GO  
--Open 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 cannot encrypt one EKM key with another EKM key.

SQL Server does not support signing modules with asymmetric keys generated from EKM provider.

EKM provider enabled Server Configuration Option

Enable TDE on SQL Server Using EKM

Extensible Key Management Using Azure Key Vault (SQL Server)

See Also

CREATE CRYPTOGRAPHIC PROVIDER (Transact-SQL)
DROP CRYPTOGRAPHIC PROVIDER (Transact-SQL)
ALTER CRYPTOGRAPHIC PROVIDER (Transact-SQL)
sys.cryptographic_providers (Transact-SQL)
sys.dm_cryptographic_provider_sessions (Transact-SQL)
sys.dm_cryptographic_provider_properties (Transact-SQL)
sys.dm_cryptographic_provider_algorithms (Transact-SQL)
sys.dm_cryptographic_provider_keys (Transact-SQL)
sys.credentials (Transact-SQL)
CREATE CREDENTIAL (Transact-SQL)
ALTER LOGIN (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
OPEN SYMMETRIC KEY (Transact-SQL)
Back Up and Restore Reporting Services Encryption Keys
Delete and Re-create Encryption Keys (SSRS Configuration Manager)
Add and Remove Encryption Keys for Scale-Out Deployment (SSRS Configuration Manager)
Back Up the Service Master Key
Restore the Service Master Key
Create a Database Master Key
Back Up a Database Master Key
Restore a Database Master Key
Create Identical Symmetric Keys on Two Servers