0

We use an external HSM device to create and store keys and use it for TDE. Our auditors are asking questions about where the keys are store. So obviously the key is stored in the external HSM device.

In this link, under step 5,

USE master ;  
GO  
CREATE ASYMMETRIC KEY ekm_login_key   
FROM PROVIDER [EKM_Prov]  
WITH ALGORITHM = RSA_512,  
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;  
GO  
  • Does this create a copy of the key in master DB also?
  • In case of TDE, how often does SQL Server has to contact the EKM device? Does it have to contact the ekm device veytime while reading data file from disk?
1

Does this create a copy of the key in master DB also?

AFAIK, no.

In case of TDE, how often does SQL Server has to contact the EKM device? Does it have to contact the ekm device veytime while reading data file from disk?

This depends on the HSM, the driver they provide which you need to have installed and loaded into the SQL Server process space but generally it will be cached for an undetermined period of time.

0

Does this create a copy of the key in master DB also?

Not according to the documentation. The asymmetric key is stored in your EKM device. There is a 'pointer' record in sys.asymmetric_keys which provides the information for retrieving the key from the EKM, but this requires credentials and permissions to access.

In case of TDE, how often does SQL Server has to contact the EKM device? Does it have to contact the ekm device veytime while reading data file from disk?

The Asymmetric key created in your EKM device is used to protect the database encryption key which is stored in the boot page of the database. When the DB starts, the DEK is decrypted using the asymmetric key or certificate it was protected with, in your case the EKM's asymmetric key.

The DEK is used to encrypt/decrypt the data on disk, so the EKM should be contacted only once on startup of the database to decrypt the DEK.

Once decrypted, SQL Server can use the DEK to decrypt data when reading from disk without having to hit the EKM again.

See Encryption Hierarchy for more information

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.