Always Encrypted Assessment and online encryption in SQL Server Management Studio 21

Always Encrypted is a feature in Azure SQL and SQL Server that enables customers to protect their sensitive data from unauthorized access. However, not all columns in a database are suitable for encryption, as some of them may have data types or constraints that are incompatible with Always Encrypted. Customers need a way to quickly and easily identify the columns that can be encrypted and identify columns that are not eligible for Always Encrypted.

With the launch of SQL Server Management Studio 21, we're excited to introduce a new assessment feature in the Always Encrypted Wizard. This assessment allows users to evaluate the tables in a selected database or choose specific tables to analyze. It identifies columns that are suitable for encryption and highlights those that are incompatible with Always Encrypted due to their data type, constraints, and other factors. For each field, the wizard provides a detailed list of restrictions, explaining why certain columns cannot be encrypted.

Where can you find the Always Encrypted Wizard?

You can launch the wizard at three different levels, depending on your needs:

1. Database Level: Ideal if you want to encrypt multiple columns across different tables.
2. Table Level: Perfect for encrypting multiple columns within the same table.
3. Column Level: Best for encrypting a single, specific column.

To get started, connect to your SQL Server using the Object Explorer component of SQL Server Management Studio. Simply right-click on the database, table, or column you wish to encrypt and select "Always Encrypted Wizard...".

Column Selection Page

The Column Selection page has undergone some slight changes. You still need to select the columns you want to encrypt, re-encrypt, or decrypt, and define the target encryption configuration for these columns. To encrypt a plaintext column (one that isn't encrypted), you can choose an encryption type—either Deterministic or Randomized—and assign an encryption key to the column. Additionally, we've introduced a global checkbox that allows you to apply the same encryption type to all selected columns.

Column Assessment Page

When you press the Next button, the selected tables and columns are assessed for their suitability for Always Encrypted or Always Encrypted with secure enclaves. The assessment starts automatically by showing a status bar and a list of the tables and columns it is currently assessing, which are done, and which are to do. The assessment will check if a table column meets the requirements for Always Encrypted or Always Encrypted with secure enclaves based on the limitations that are documented here.

If a column does not meet the requirements, the assessment will display an "Error" status for that column. By clicking on the Messages link, you can get detailed information about why that specific column cannot be encrypted.

You can proceed with encrypting the columns that have successfully passed the assessment by simply checking the checkbox. The wizard will automatically skip any columns that didn't pass the assessment. Additionally, you have the option to export the results to a CSV or text file by clicking on the Report button.

The subsequent steps of the wizard remain unchanged.
One final note: the assessment feature can also be used to evaluate all tables and columns in your database . This provides a comprehensive overview of all the columns that are eligible for encryption.

Run Settings Page

The wizard supports two approaches for setting up the target encryption configuration: online and offline.

With the offline approach, the target tables (and any tables related to the target tables, for example, any tables a target table have foreign key relationships with) are unavailable to write transactions throughout the duration of the operation. The semantics of foreign key constraints (CHECK or NOCHECK) are always preserved when using the offline approach.

With the online approach, the operation of copying and encrypting, decrypting, or re-encrypting the data is performed incrementally. Applications can read and write data from and to the target tables throughout the data movement operation, except the last iteration, the duration of which is limited by the *Maximum downtime* parameter. To detect and process the changes applications can make while the data is being copied, the wizard enables Change Tracking in the target database. Because of that, the online approach is likely to consume more resources on the database side than the offline approach. The operation may also take much more time with the online approach, especially if a write-heavy workload is running against the database. The online approach can be used to encrypt **one table at a time** and the table must have a primary key. By default, foreign key constraints are recreated with the NOCHECK option to minimize the impact on applications. You can enforce preserving the semantics of foreign key constraints by enabling the *Keep check foreign key constraints* option.

Here are the guidelines for choosing between the offline and online approaches:

Use the offline approach:

• To minimize the duration of the operation.
• To encrypt/decrypt/re-encrypt columns in multiple tables at the same time.
• If the target table doesn't have a primary key.

Use the online approach:

• To minimize the downtime/unavailability of the database to your applications.

Conclusion

The new assessment feature of the Always Encrypted Wizard in SQL Server Management Studio 21 provides a valuable tool for users to evaluate the suitability of their database columns for encryption. By identifying columns that are compatible with Always Encrypted and highlighting those that are not, this feature helps users make informed decisions about their data protection strategies. The ability to export assessment results and apply encryption configurations to multiple columns simultaneously further enhances the efficiency and effectiveness of the encryption process. Overall, this new feature represents a significant step forward in simplifying and streamlining the implementation of Always Encrypted in SQL Server and Azure SQL databases.

Next Steps

Configure column encryption using Always Encrypted Wizard
Tutorial: Getting started with Always Encrypted

We’d love to hear your feedback – please contact us at alwaysencryptedpg@microsoft.com