In today’s data-driven world, protecting sensitive information is more critical than ever. With increasing security threats and stricter compliance regulations, businesses need reliable solutions to safeguard their data. Enter Always Encrypted, a powerful feature in SQL Server designed to ensure that sensitive data remains encrypted, both at rest and in transit, without exposing it to database administrators or unauthorized users. This end-to-end encryption model helps organizations protect personally identifiable information (PII), credit card details, and other confidential data from prying eyes, providing a seamless yet secure way to manage and query encrypted data. In this blog, we’ll explore how Always Encrypted works, its benefits, and how it can be a game-changer for your data security strategy.

The Always Encrypted feature was available only on the Enterprise and Developer editions of SQL Server 2016. Later, this feature was made available on all editions, with SQL Server 2016 SP1. Always Encrypted has the ability to encrypt data even at the column level.

There are several ways to configure the Always Encrypted feature:

• Using the Always Encrypted wizard
• Configuring AE using SSMS
• Create Master Key and Encryption Key using T-SQL and enabling encryption
• Configuring Always Encrypted using PowerShell

Overview of the Always Encrypted Feature

Always Encrypted feature is a handshake mechanism used to encrypt and decrypt data. Encryption here is achieved using certificates, and can be done only by users with access to the relevant certificates. To make a database column Always Encrypted, you must specify the encryption algorithm and the cryptographic keys that are used to protect the data. Always Encrypted needs two keys:

1. Column Encryption Key (CEK)
2. Column Master Key (CMK)

A Column Encryption Key is used to protect and encrypt data in a column. A Column Master Key is used to protect the (one or more) column encryption keys. The information about the Column Master Key is stored in external key stores like:

• Azure Key Vault: A key vault used to safeguard and manage cryptographic keys and secrets used for encryption and decryption of sensitive data within Microsoft Azure.
• Windows Certificate Store: A certificate container built into Windows that stores and manages the certificates.
• Hardware Security Module (HSM): A hardware device specially designed to securely store sensitive data

Selecting Deterministic or Randomized Encryption

Always Encrypted supports two types of encryption: randomized and deterministic

• Deterministic encryption
  • The same encrypted Key for a given value is generated, every time.
  • Binary2 sort order collation must be used to setup deterministic encryption on a column.
  • Heuristically studying the patterns of the contents of the column could reveal the contents, thereby making it more susceptible to hacking
• Randomized encryption
  • This method is more robust and secure, and the patterns are less likely to be predictable due to its random generation of the key for a given value.
  • The limitation with this type of encryption is that searching, join, group and, indexing is not possible

In an age of centralized or remote management of data, it is important that the enterprises add an abstraction layer to their data. This way, those who manage the data on a day-to-day basis, such as database administrators are not able to view or use the data. At the same time, those in the enterprise who own the data, have complete access to the data, even though they may not necessarily manage it.

Apart from being the layer of abstraction, Always Encrypted also ensures encryption of data during transit, thereby protecting it from sniffers—typically those involved in attacks such as Man in the Middle.

Configuring Always Encrypted

To set up Always Encrypted, we need to generate the following:

1. Key metadata
2. Encryption properties of the selected database columns, and/or encrypting the data that may already exist in columns that need to be encrypted.

There are multiple ways achieve this encryption, in this blog we will be doing this through SSMS

• Using object explorer select a Database in this case the DB will DS
• Go to the security tab
• Select the Always encrypted keys

• First create column encryption key
• Then create column master key

• Browse the Columns tab
• Right-click the column and select Encrypt Column
• Select the Encryption Type: Select either of the available options, since Always Encrypted supports two types of encryption: Randomized and Deterministic
• Use the drop-down, and select the Column Encryption Key, which is already tied with the Column Master Key

• Query the table and view the encrypted column. In the below screenshot, the Salary column is encrypted.

In order to decrypt the column, the following settings should be enabled in the SSMS client

• First, add Column Encryption Setting = Enabled in the Additional Connection Parameters in the SSMS Connect to Server window.

Now you can query the table and see the records intact.

See that how beautiful the feature is to protect the critical information in our database from any middle man. Hope this blog was informational, do try and implement this and keep your information safe…

Thanks for your time, and happy learning!!.

Lochan R