Sql-server – Why use asymmetric keys in SQL Server

encryptionsql server

I've been reading multiple resources on SQL Server Encryption, and everyone does it basically the same way, but no one explains why we use asymmetric keys in SQL Server. And by this I mean when we follow "proper" procedure and encrypt data symmetrically first, followed by an asymmetric key or certificate as in this MSDN heirarchy:

enter image description here

So if I have data, first I encrypt it with a symmetric key for speed and avoidance of re-encryption if compromised. We then secure that key with a second-tier of encryption.

Now lets assume I secure the second key with a password. What advantage does a Certificate, Asymmetric Key, or Symmetric Key have in this scenario? It seems to me we never utilize the public aspect of a Certificate or Asymmetric key using this strategy since we are only using the decryption aspect. So shouldn't be be agnostic to which of the three methods we choose? We can revoke access to all three key types and grant permissions to use all three keys. Why not use another symmetric key?

The only difference I see here is that you can protect a Cert or Asym key with the DMK so users need not enter a password. This makes access easier at the expense of losing the security of forcing users to enter that second password.

Is this a correct understanding?

Best Answer

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.

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

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

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

Related Question