Always Encrypted Assessment in SQL Server Management Studio 21

Microsoft

Nov 06, 2024

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:

Database Level: Ideal if you want to encrypt multiple columns across different tables.
Table Level: Perfect for encrypting multiple columns within the same table.
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.

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.