Encryption is one of the most important aspect of security in any RDBMS system including SQL Server. Using encryption we can protect the data even if someone has got a hold or access to the database files, and column level encryption is protecting the confidential data in your columns using a Symmetric Key.
The below diagram has helped me a lot in understanding on how to get the Symmetric key for the encryption.
The above pic helps in deciding how to get the Symmetric Key (SK) which is required to protect the data. To get the SK, we can choose any path like:
1. Creating a SK using Password or,
2. Creating a certificate using password and using that certificate to create the SK, or any path that can be traversed to reach the “Encrypted Data”.
There is an awesome MS-Doc article about implementing CLE that you can reference.
Things to consider when using CLE:
1. We need to open the Symmetric key whenever accessing the underlying plain text data and that holds the main benefit of using CLE as only those who have the access to either the password/certificate used for creating the SK can open the Symmetric Key and thus decrypt the encrypted data.
OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE Cert_name/Password = ‘passwordforsk’; GO
2. Once you no longer to need to work on the unencrypted data you need to close the Symmetric Key.
CLOSE SYMMETRIC KEY key_name GO
3. To insert values in an encrypted column we need to use the ENCRYPTBYKEY function like below:
INSERT Employees VALUES (1, 'SomeName', 'SomeValue’, ENCRYPTBYKEY(KEY_GUID('key_name’), 'ValueToEncrypt'));
4. To decrypt and read the data user must have the permission on the chain of encryption keys or certificate used to create the SK. Example for providing permission to a SK encrypted by a certificate:
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO User; GO GRANT VIEW DEFINITION ON Certificate::Certificate1 TO User; GO
5. When taking backups and restoring it to some other server we don’t need to do any extra steps in carrying the keys to the newer location as the keys move along with the database (As they are stored in the database only). We have to use DECRYPTBYKEY function in the SELECT to see the decrypted value for a column.
6. Log Shipping, Always On, Replication work flawlessly with CLE as long as we have restored the keys created at source to the destinations (secondary/replica/ subscriber).
7. When implementing CLE we also have to make some changes at application end, as it requires opening and closing the SK and also using the functions ENCRYPTBYKEY and DECRYOTBYKEY, and we need to implement this logic at the application end too.
8. It is encryption at rest i.e. the data remains encrypted when inside the database files, once the data is read and decrypted using the encryption key, it will remain in plain text only as long as it stays in the memory.