Blog Post

Azure SQL Blog
3 MIN READ

Always Encrypted vs Always Encrypted with secure enclaves

PieterVanhove's avatar
PieterVanhove
Icon for Microsoft rankMicrosoft
Jun 06, 2024

Introduction

Encryption is a vital technique for protecting sensitive data from unauthorized access or modification. SQL Server and Azure SQL Database offer two encryption technologies that allow you to encrypt data in use: Always Encrypted and Always Encrypted with secure enclaves. In this blog post, we will compare these two technologies and highlight their benefits and limitations.

 

What is Always Encrypted?

We began our journey towards the confidential computing vision in 2015 and we introduced the first version of Always Encrypted in SQL Server 2016 and Azure SQL Database. Always Encrypted is a client-side encryption technology where the client driver transparently encrypts query parameters and decrypts encrypted results. The data gets encrypted on the client side (inside the client driver) before being stored in the database. The data is never decrypted inside the database. The encryption keys are never exposed to the database engine, ensuring that the data stays secure even if the database is breached.

 

Always Encrypted provides strong encryption, but computations on protected data is limited to just one operation – equality comparison, which is supported via deterministic encryption. All other operations, including cryptographic operations (initial data encryption or key rotation) and richer queries (for example, pattern matching) aren't supported inside the database. Users need to move their data outside of the database to perform these operations on the client-side.

 

 

What is Always Encrypted with secure enclaves?

Always Encrypted with secure enclaves is an enhancement of Always Encrypted that enables rich computations on encrypted data. When processing SQL queries, the database engine delegates computations on encrypted data to a secure enclave. The enclave decrypts the data and performs computations on plaintext. This can be done safely, because the enclave is a black box to the containing database engine process and the OS, so DBAs or machine admins cannot see the data inside the enclave.

 

 

Always Encrypted supports the following enclave technologies (or enclave types):

 

What are the differences between Always Encrypted and Always Encrypted with secure enclaves?

 

  Always Encrypted Always Encrypted with secure enclaves
Version Introduced in SQL Server 2016 and Azure SQL Database in 2015. Also available in Azure SQL Managed Instance and CosmosDB Available from SQL Server 2019 and later, as well as in Azure SQL Database
Trusted Execution Environment Ensures data confidentiality by encrypting it on the client side. Utilizes a secure enclave. which is a trusted execution environment that can safely access cryptographic keys and sensitive data in plaintext without compromising confidentiality.
Initial Encryption and key rotation Encrypts data on the client side Cryptographic operations on database columns inside the secure enclave on the server side. The data can stay inside the database.
Confidential Queries Limits queries to equality comparisons only (only available with deterministic encryption) Allows richer confidential queries, including pattern matching, range comparisons, and sorting.

 

In essence, Always Encrypted with secure enclaves provides more flexibility and functionality for querying and managing encrypted data while maintaining a high level of security. It leverages secure enclave technology to allow certain computations to be performed on encrypted columns directly inside the database, without exposing sensitive data outside of the enclave.

 

Why do people still use Always Encrypted instead of Always Encrypted with secure enclaves?

 

I noticed that many customers are still creating new databases without using an enclave, even though we have learned that Always Encrypted with secure enclaves offers you more flexibility and functionality. So, what is the explanation for this? I contacted many of these customers and the main reasons are the following:

  • They are not aware of Always Encrypted with secure enclaves.
  • They don't need the functionality. They only need to encrypt PII information but there is no need to use it for filtering or in a where-clause.
  • Their application already works with Always Encrypted and they don't have resources, time or budget to migrate to Always Encrypted with secure enclaves.

So, I'm interested in your opinion. Are you using Always Encrypted? Or are you planning to use it? What is the reason why you're not using it with enclaves? Please let me know on this form. It would be great to get some more insights.

 

Learn more

 

Updated Jun 05, 2024
Version 1.0
azure sql
Azure SQL Security
sql server
  • Francesco_beqom's avatar
    Francesco_beqom
    Copper Contributor
    Jun 11, 2024

    I'm just here because of Brent Ozar. 
    So you say "[...] DBAs or machine admins cannot see the data inside the enclave." but you were already saying this of Always Encrypted around 5 years ago. 

    So what Secure Enclave has that Always Encrypted doesn't have?
    You tell me why should I pass to Secure Enclave, because frankly I don't understand. What are the "plus"?
    Using Always Encrypted is complicated enough and comes with a bible of limitations. (it literary means rewriting the code. We wanted to adopt it but the effort was too big)

     

  • devakishore's avatar
    devakishore
    Copper Contributor
    Jun 14, 2024

    Secure Enclaves supports range and like queries as well as indexes on columns with randomized encryption. Also gives ability to rotate CEK 

  • Francesco_beqom's avatar
    Francesco_beqom
    Copper Contributor
    Jun 14, 2024

    I would say that Dynamic Data Masking and Always Encrypted with secure enclaves are two different products. 

    • Dynamic Data Masking: you use it when you want to mask data based on privileges. There is no encryption. If someone steal the DB they can access everything.
    • Always Encrypted with secure enclaves: You use it when you actually want to encrypt data. The data are encrypted/decrypted on the fly and shown as plain text to the end user. If someone steal the DB the data are encrypted. Data are also encrypted during the traffic.
  • PieterVanhove's avatar
    PieterVanhove
    Icon for Microsoft rankMicrosoft
    Jul 09, 2024

    Hi Francesco_beqom,

     

    As devakishore mentioned, Always Encrypted with secure enclaves gives you much more flexibility compared to Always Encrypted.

    The first version of Always Encrypted didn't use an enclave, so you were limited when you want to use an encrypted column in a where clause.

    The enclave allows your to use rich confidential queries and use like, < >, BETWEEN, etc. in the where-clause on encrypted columns. Another advantage of the enclave is that it is used during the initial encryption. So, the data doesn't have to leave the database anymore to perform the initial encryption.

    More information on how Always Encrypted with secure enclaves works can be found in our documentation or in this blog post.

     

    Regarding your comment on Dynamic Data Masking and Always Encrypted. That is correct, DDM is not an encryption feature and the data is still in plaintext in the database. 

  • Francesco_beqom's avatar
    Francesco_beqom
    Copper Contributor
    Jul 22, 2024

    Thank you PieterVanhove , this is more clear now. I was just having a look at the limitations: 

    • No Clustered indexes, no PK or FK or unique key constraints: What if I have those in place right now? Do I need to remove all those before I enable secure enclave? Or will those be dropped as soon as I enable enclave? 
    • Only nested loop joins are supported: this will significantly slow down the performances. Does it means that all hash joins and merge joins will become nested loop joins automatically? Or they will just fail? 
    • Escape character in LIKE operator: all WHERE column_directory LIKE '%D:\user\franc\%' will fail. This is heavy. This means we can give up using the LIKE operator in mostly XML, JSON, etc...
  • saokimsong's avatar
    saokimsong
    Copper Contributor
    Jul 31, 2024

    Is Always Encrypted supported for my .NET client application running on Linux (in a Docker container)? Currently, we are not using Azure Key Vault. Thanks!

  • PieterVanhove's avatar
    PieterVanhove
    Icon for Microsoft rankMicrosoft
    Aug 09, 2024

    Hi Francesco_beqom,

     

    No Clustered indexes, no PK or FK or unique key constraints

    Enabling the enclave has no impact on your database. However, you will get an error message when you try to encrypt a column that is used in the clustered index or is PK or FK. You should remove this first before encrypting the column. I understand that this is not easy to do.

     

    Only nested loop joins are supported

    This limitation doesn't apply to Azure SQL Database or SQL Server 2022.

     

    Escape character in LIKE operator

    Escape characters are indeed not supported. Just to be clear, Always Encrypted is not supported for XML or JSON columns.

  • Francesco_beqom's avatar
    Francesco_beqom
    Copper Contributor
    Aug 09, 2024

    Thank you PieterVanhove . But now I have another question: if I enable Always Encrypted can I: 

    • Decide to encrypt the whole database 
    • Decide to encrypt only some table
    • Decide to encrypt only some columns in some tables 

    Thank you