Getting Started With SQL Server Always Encrypted

Azure SQL Database and SQL Server 2016 introduced a new security feature for SQL Server databases called Always Encrypted. Always Encrypted is a feature designed to protect sensitive data, such as national/regional identification numbers (Social Security numbers for those in the USA), credit card numbers, and other highly sensitive types of data.

Always Encrypted allows customers to encrypt data within the database, not just at rest like Transparent Data Encryption. This allows for data to be encrypted inside the client applications and never reveal the encryption keys to the Database Engine. Now we have separation between those who own the data and who can view it. This can also restrict those who manage the data from seeing unencrypted data, for example, IT administrators. Unless you have the encryption keys, you cannot decrypt the data. This provides organizations a higher level of confidence that highly sensitive data is protected from malicious attacks.

To get started with Always Encrypted, you’ll need a column master key and column encryption key. The column encryption key is used to encrypt data in an encrypted column, where the column master key is a key-protecting key that encrypts one or more column encryption keys.

Each implementation of Always Encrypted I’ve been involved with has ultimately come down to a decision on which encryption algorithm to use. The options are deterministic and randomized.

Randomized encryption is exactly what it sounds like. It uses a method that encrypts data in a less predictable manner. Randomized by nature is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns, which makes it much more difficult to work with.

Deterministic encryption will always generate the same encrypted value for a given plaintext value. This allows for point lookups, equality joins, indexing and grouping on encrypted columns. This is a bit less secure than randomized and could allow unauthorized users to guess information about encrypted values by examining the patterns in the encrypted column.

In my experience, extensive planning and testing should be done before attempting to use Always Encrypted, however it can be a very valuable tool to help secure data when you are able.

To see a brief demo of using Always Encrypted, check out my video.