SQL Server Management Studio improvements for Always Encrypted

Microsoft

Nov 30, 2023

A couple of months ago we released a new version of the Always Encrypted Wizard that now supports secure enclaves and in-place encryption. With the new release of SSMS 19.2 we introduced a bunch of extra performance improvements and a big Always Encrypted Wizard enhancement. In this blog post I’m going to give you an overview of the new wizard capability and the performance improvements.

Always Encrypted Wizard – Enable Secure Enclaves

We made the wizard smart! When your database is not configured with a secure enclave, the wizard will give you the opportunity to enable a secure enclave. Using an enclave enables you to run cryptographic operations in-place, without moving data out of the database. We strongly recommend you enable a secure enclave to improve the performance of the initial encryption.

To enable the secure enclave, simply click the “Enable Secure Enclaves” button.

This will only take a couple of seconds to enable a VBS enclave on the database.

Performance Improvements

DacFx

While testing a very simple Always Encrypted scenario, encrypting 1 record with the wizard, we noticed that DacFx sends 3000+ T-SQL calls to the database. Out of these calls only 2-3% were unique statements. These commands not only unnecessarily overload the SQL Server, but it also added considerable delay in the overall encryption process.

We have optimized the repeating T-SQL calls and improved the encryption and decryption process with approximately 20%!

SSMS and Wizard Fixes

We also made some bug fixes in SSMS that were reported to us.

SSMS wizard fails if randomly encrypted columns already exist with and index.
SSMS should not allow the user to Encrypt Columns of a database that is not in an online state.
Cancelling the AE wizard/Set-SqlColumnEncryption cmdlet does not delete temporary tables.
The table owner is changed after encryption.
Encryption of computed columns should not be allowed.