Extensible Key Management (EKM) and Hardware Security Module (HSM) in SQL Server
Extensible Key Management (EKM):
SQL Server 2008 introduced Extensible Key Management (EKM) for managing keys outside of SQL Server. Traditionally, all Symmetric and Asymmetric Keys used by SQL Server reside in the database itself; however EKM allows key creation, storage, encryption and decryption to be done outside the database using an HSM.
With the growing demand for regulatory compliance and concern for data privacy, organizations are needs to come up with strong security solutions. One of the approaches followed is use of encryption, but this is often impractical for a strong security solution using only database encryption management tools.
Hardware Security Module (HSM) – 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.
Hardware security module (HSM) is a physical computing device that safeguards and manages digital keys for strong authentication and provides cryptoprocessing. These modules traditionally come in the form of a plug-in card or an external device that attaches directly to a computer or network server. HSMs may possess controls that provide tamper evidence such as logging and alerting and tamper resistance such as deleting keys upon tamper detection. Each module contains one or more secure cryptoprocessor chips to prevent tampering and bus probing.
An HSM should adhere to one or more recognized security and operational standards defined by various industry groups, such as the Federal Information Processing Standard (FIPS), Common Criteria, etc. An HSM deployment, and supporting operational practices, should also address the requirements of reputable business processes and security auditors to provide the highest degree of protection for the CA and its root keys.
A number of vendors (CREN, Safenet, Thales, etc) 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. 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.
Extensible Key Management – 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 Enterprise Edition feature. By default, Extensible Key Management is off. To enable this feature in SQL Server, use the sp_configure command that has the following option and value, as in the following example:
sp_configure 'show advanced', 1
Reconfigure with override
sp_configure 'EKM provider enabled', 1
Reconfigure with override
To disable the feature, set the value to 0.
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.
You can use Extensible Key Management for a username and password combination or other methods defined by the EKM driver. 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.
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.
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;
--Open database key
OPEN SYMMETRIC KEY Key1
DECRYPTION BY EKM_AKey1
The main idea here is to get a HSM solution from a Vendor and then integrate it with the SQL Server and store the encryption keys on a HSM. The data can be encrypted/decrypted with the keys (Symmetric key, Asymmetric key, certificate) which are stored in HSM and only authorized users can gain access to these keys based on process defined by the HSM provider.