Extensible Key Management (EKM)
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.
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 2012.
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.
Warning
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 Identify 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 |
|
Asymmetric Key encryption |
|
EncryptByKey(key_guid, 'cleartext', …) |
|
DecryptByKey(ciphertext, …) |
|
EncryptByAsmKey(key_guid, 'cleartext') |
|
DecryptByAsmKey(ciphertext) |
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.
Related Tasks
EKM provider enabled Server Configuration Option
See Also
Reference
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)
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)
Concepts
Back Up and Restore Reporting Services Encryption Keys (SSRS Native Mode)
Delete and Re-create Encryption Keys
Add and Remove Encryption Keys for Scale-Out Deployment
Back Up the Service Master Key