Facts about SQL Server Data Encryption & Decryption

1. Encryption is supported by SQL Server Developer and Enterprise editions only.

2. Encryption is the conversion of readable plaintext into ciphertext, which cannot be easily understood by unauthorized people.

3. Decryption is the process of converting ciphertext back into its original form so it can be understood.

There is no SSMS interface to create encryption keys, and certificate. They can be created only by T-SQL.

4. Two types of encryption can be implemented in SQL Server: Cell-level encryption and Transparent Data Encryption (TDE). You can implement any one or both in a database.

5. Data can be encrypted by any of following methods:

·         Password (ENCRYPTBYPASSPHRASE function)

·         Hashing (HASHBYTES function)

·         Symmetric Key

·         Asymmetric key

·         Certificate

·         TDE

6. TDE secure the data on disk. TDE encrypt data while saving to disk in data files or backup files and decrypt data while reading from disk. TDE ensured that database files or backup files cannot be stolen and restored on another machine without the necessary DMK. No application and database design changes are required for TDE.

7. Cell-level encryption is the most part of encryption and it is maintained by multi level hierarchy of encryption keys. Particular value in a table can be encrypted with this type of encryption.

8. Following is the hierarchy of encryption keys (following scenario is one of many possible key hierarchy configurations):

Table data encrypted by Symmetric key

                Symmetric key encrypted by Asymmetric key or certificate

                                Asymmetric key encrypted by Database Master Key (DMK)

                                                DMK encrypted by Service Master key (SMK)

                                                                SMK encrypted by Windows Data Protection API

9. Best practices for cell-level data encryption

·         Encrypt high-value and sensitive data only.

·         Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.

·         Password-protect keys and remove master key encryption for the most secure configuration.

·         Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.

·         Always back up your database to back up your symmetric and asymmetric keys.

10. Data encrypted by cell-level encryption can only be stored in a varbinary data type column. So to encrypt the data of existing table needs table structure changes.

11. Cell-level encryption is implemented through many encryption and decryption functions. So it needs T-SQL code changes to implement DML operations.

12. Certificate is preferred against Asymmetric key because certificate can be backed up to a file while asymmetric key not.

13. The automatic key management hierarchy works as follow (take the above mentioned scenario):

14. The Windows Data Protection API automatically decrypts SMK (Service Master Key).

                SMK automatically decrypts DMK (Database Master Key).

                                DMK automatically decrypts asymmetric key.

                                                Asymmetric key automatically decrypts symmetric key

                                                                Symmetric key automatically decrypts table data.

So by default, the automatic key management does not provide any more security because data is decrypted automatically when accessed.
15. Data can be protected if we take control over automatic key management. This can be done by following three methods:

1. Granting authorization on key to authorized users only.
2. Encrypting the data or keys with secure password.
3. Extensible Key Management (EKM).

16. Password can be used to encrypt table data and any level of key up to DMK.