SQL Server has had the native ability to encrypt data since SQL Server 2005. This included functionality that could be used to encrypt individual items and columns of data as well as the Transparent Data Encryption (TDE) feature which was available with the enterprise edition of SQL Server and could be used to encrypt all data where it is stored on disk. We then didn’t see significant additions to the features available for encryption until 2016 when Microsoft added Always Encrypted for column encryption. In SQL Server 2019 Microsoft made TDE available in standard edition, and also in SQL Server 2019 the ability to use enclaves was added to Always Encrypted to improve the available functionality for interacting with encrypted data. Finally in SQL Server 2022 further enhancements were made to the set of functionality available when working with Always Encrypted with enclaves.

We can see Always Encrypted (which was introduced in 2016) as the successor to previous methods of column encryption, and in later posts we will go into a high level of detail on Always Encrypted, although we’ll also cover the older methods in brief.

The table below shows the list of encryption features in SQL Server and what each tool is intended to protect.

Encryption features available in SQL Server

Feature	What is Protected
Transparent Data Encryption (TDE)	Data saved to disk. This includes data files, transaction log files, backup files and database snapshots.
Backup Encryption	Backup files.
Always Encrypted	Data stored in columns. With Always Encrypted the data is protected on disk, in memory and in-transit across the network.
Transport Layer Security (TLS)	Network traffic. TLS protects data in-transit across the network as well as commands executed against the database server.
Hashing and Salting	This is not strictly encryption, but we generally use it to protect passwords.
Encryption Functions	Data stored in columns. Here we are referring to the encryption functions introduced in SQL 2005 that pre-date Always Encrypted.
Extensible Key Management (EKM)	This provides extra protection and ease of management for encryption keys by enabling them to be stored with an external provider.

Let’s take a brief look at each of these features in turn.

TDE

TDE protects our data stored on disk, what we often refer to as “at-rest” data. It offers good protection against the scenario where the file system is accessed, and an attacker might attempt to retrieve data directly from the database files themselves – or copy the backup files so they can be restored to another SQL Server to access the data. It doesn’t protect us at all though where an attacker may have access to query the database directly. The “transparent” part of the name refers to the fact that TDE works transparently in the background with no impact on our queries or other application functionality. TDE protects all of the data in a database, unlike methods of column encryption which usually target specific types of information to encrypt.

Backup Encryption

Backup encryption just encrypts our backup files. This includes full backups, differential backups and log backups. This is particularly useful where we might store backups, possibly on tape, off site and want to make sure they are inaccessible if stolen. TDE also does this for us, so we only consider using backup encryption where we can’t use TDE for some reason.

Always Encrypted

Always Encrypted is a form of column encryption. It works hand in hand with the client driver that your application uses to connect to and query the database to ensure that data remains encrypted all the way to the point it reaches your application. That’s what the “always” part of the name refers to. Data is protected at-rest, in-memory and in-transit across the network. Encryption and decryption actually take place within the client driver rather than within SQL Server.

On this blog we will look in depth at two flavors of Always Encrypted. We have the basic version that was introduced in SQL Server 2016, and Always Encrypted with Secure Enclaves that was added in SQL Server 2019. What’s nice about Always Encrypted is that encryption and decryption are carried out automatically for you by the client driver so in many cases you may not even have to make code changes. There are limitations on how you can interact with encrypted data though. The version with enclaves removes some of those restrictions by allowing certain activities to place in a secure portion of memory (called an enclave) on the database server. The use of enclaves does however come with an extra overhead in setup and management.

TLS

TLS is used to encrypt network traffic. That means that data and queries sent between the application and database server are all encrypted. This is similar to SSL, which most people are familiar with for encrypting internet traffic (SSL in most cases actually uses the TLS protocol).

Hashing and Salting

Hashing and salting isn’t actually encryption because it is a one-way process. Hashing is where we run a value through a function that produces a seemingly random output. That output will always be the same for the same input value, but cannot be reverse-engineered to find the original value. Salting is a method to provide extra security for hashed values. Hashing and salting is considered the best practice for storing passwords as it means we don’t even need store actual passwords – so there should be no way for an attacker to access them.

Encryption Functions

Here we refer to the set of encryption functions that SQL Server implements to allow you to encrypt your own data. I see Always Encrypted as the successor to these functions and would recommend you use that where possible. Encryption using the functions is a bit more limiting, a bit less secure and a bit harder to implement than with Always Encrypted. There may be some scenarios where you want to use them though, so we’ll cover them in brief later on – though hopefully in enough detail that it tells you everything you need to know. 

EKM

Most encryption is based on keys, and we need to think about how and where we manage them over time. EKM is functionality that allows you to store them outside of your server, either on a piece of kit that sits in a rack in your server room called a Hardware Security Module (HSM), or more commonly these days, using a cloud service like Azure Key Vault. You don’t need to use EKM in order to implement a secure encryption strategy but it’s certainly worth considering due to the ease of management that comes from having all of your keys in one place. It’s also easier to manage policies such as access control when you take a centralized approach to storing your keys.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.