The question of encryption seems to be coming up a lot recently. I’ve had a number of people asking me about how to go about encrypting SQL Server.
SQL can encrypt our data at a number of different levels and gives us a quite a few options when doing so. I want to use this post to put together a matrix so you can easily see which method of encryption suits your purpose. I’ll look in to each method in more detail in a future series of posts.
| Transport Layer Security | Transparent Data Encryption (TDE) | Backup Encryption | Cell Level Encryption | Always Encrypted | |
| Encrypts Data at Rest |  |  | | | |
| Encrypts Data in Flight | | | | | |
| Encrypts Data in Memory | | | | | |
| Encrypts SQL’s Backup Files | | | | * | * |
| Encrypts Full Database or Selected Data | All Data | Full Database | All Data | Selected Data Only | Selected Data Only |
| SSL Cert Required | | | | | |
| Uses Windows Data Protection API (DPAPI) | | | | | |
| External Key Store Required | | | | | |
| Application Changes Required | | | | | The application does need to be ‘Always Encrypted aware’ |
| Performance Impact | Minimal | Moderate | Minimal | Minimal | Minimal |
| Impact on Indexing Strategy | None | None | None | Encrypted Data will Perform Poorly as Part of an Index or Predicate | Indexed or Values used as part of a predicate must use deterministic encryption. |
Encrypts Data at Rest
The physical data file is encrypted. The data may be unencrypted within SQL’s memory and user with read access to the database will be able to access the data. Protects against theft of the data files themselves.
Encrypts Data in Flight
Data is encrypted in transfer between SQL Server and the client. Protects against packet sniffing attacks.
Encrypts Data in Memory
Data is encrypted within SQL’s Memory. This also means that access to a database doesn’t necessarily grant access to the encrypted data without access to the correct keys. Protects sensitive data from users who may otherwise have access to the database, also against users gaining unauthorised access to the database (either through data file theft or unauthorised access via SQL Server).
Encrypts SQL Backup Files
The SQL backup files themselves are encrypted and can’t be restored without access to the encrypting certificate and private key. Protects against theft of backup files.
Encrypts Full Database or Selected Data
Some methods of encryption encrypt all the data in a database, others only encrypt selected, sensitive data.
SSL Cert Required
An SSL certificate is required, this can either be a certificate issued by a certificate authority or a self signed certificate.
Uses Windows Data Protection API (DPAPI)
Required certificates and keys generated in SQL, using DPAPI. Special care should be taken here to ensure that all required certificates and keys are backed up. Without these, you’ll be unable to access any encrypted data or restore any encrypted backups.
External Key Store Required
Applications will need access to an external key store containing all required keys and certificates in order to decrypt data.
Application Changes Required.
Cell level encryption, for example uses SQL functions to encrypt and decrypt data. Any implementation of cell level encryption would also need modifications in code to open and close certificates and to encrypt and decrypt data.
Performance Impact
Does the method of encryption carry a significant performance over head.
Impact on Indexing Strategy
With some methods of encryption, there are limitations on how encrypted data can be used in an index as well as in search predicates.
I hope you find this helpful for understanding what the various methods of encryption that SQL gives us can be used for and in which scenarios you may want to use a certain method.