The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.
The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.
All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.
This sounds like a case where the key_guid
in the encrypted data does not match the key_guid
for the symmetric key used to encrypt it.
When data is encrypted using a symmetric key and is then saved in a varbinary
column, the first 16 bytes are the Key_guid
of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT
can automatically locate the correct symmetric key.
Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?
select
name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
name ='key_dbKeys'
select distinct table.data from table
where
table.data is not null
After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.
declare @clean_data varchar(100)
set @clean_data='Hello World'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';
CREATE CERTIFICATE cert_dbKeys
ENCRYPTION BY PASSWORD = 'P@ssword!'
WITH SUBJECT = 'Database encryption key',
EXPIRY_DATE = '20201031';
CREATE SYMMETRIC KEY key_dbKeys
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))
OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'
insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))
CLOSE SYMMETRIC KEY key_dbKeys;
SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData
Best Answer
They both are asymmetric keys, certificates just have extra metadata and can have other functions run on them such as validation and revocation.
Yes, see above.
Yes, although you can change that by removing the private key from asymmetric keys and certificates. Additionally, the keys do not have to be stored in the physical database, you can use a HSM.
No, it means that the symmetric key is protected by the asymmetric key. This means the asymmetric key much first be successfully decrypted, the private key must be available, and the symmetric key can then be decrypted using the private key if it exists. Once the symmetric key is decrypted it can be used in operations. If the asymmetric key can not be decrypted, you will get an error and be unable to use the symmetric key as it'll still be encrypted. The data itself is protected by the symmetric key.