Encryption, Comparing your Options in SQL Server


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 SecurityTransparent Data Encryption (TDE)Backup EncryptionCell Level EncryptionAlways Encrypted
Encrypts Data at Rest✖✔✖✔✔
Encrypts Data in Flight✔✖✖✖✔
Encrypts Data in Memory✖✖✖✔✔
Encrypts SQL’s Backup Files✖✔✔✖*✖*
Encrypts Full Database or Selected DataAll DataFull DatabaseAll DataSelected Data OnlySelected 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 ImpactMinimalModerateMinimalMinimalMinimal
Impact on Indexing StrategyNoneNoneNoneEncrypted Data will Perform Poorly as Part of an Index or PredicateIndexed or Values used as part of a predicate must use deterministic encryption.
* While the backup file itself isn’t encrypted, any encrypted data in the database will also be encrypted within the backup file

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating