sql-serversslalways-encryptedtde

SQL Server SSL + TDE vs Always Encrypted


What is the difference between using SQL Server SSL (Encrypted=true in the connection string) + TDE, vs using SQL Server Always Encrypted?

With regards to RGPD, is one more adapted than the other?


Solution

  • Always Encrypted exists to solve more than just the issue of making sure data is encrypted in-transit. In fact, that's not even the primary concern that Always Encrypted solves.

    The big issue that Always Encrypted solves is that with Transparent Data Encryption (TDE), the keys and certificates which secure the encrypted data are themselves stored in the database. This could be a concern for someone considering putting their SQL Server database in the cloud, because the cloud provider then ultimately has the secrets for decrypting the data.

    With Always Encrypted, the Column Encryption Key (CEK), which is used to encrypt/decrypt column data, is stored in the database in its encrypted form. But here's the kicker - the key used to encrypt/decrypt the CEK is stored outside the database, leaving the database unable to decrypt the data on its own.

    All the database can do is

    1. Provide the encrypted CEK,
    2. provide the location of the CMK, and
    3. serve/store pre-encrypted data.

    It's up to the client to get the Column Master Key (CMK) from the key/certificate store wherever that's located, then use the CMK to decrypt the CEK, and use the decrypted CEK to encrypt/decrypt data.

    So that's the conceptual difference. Here are a couple pages that go into the details of it:

    Be aware that Always Encrypted comes with some hefty drawbacks with regard to querying data and other things. This article gives a pretty good list of limitations. Some of these drawbacks can be mitigated using Always Encrypted with secure enclaves.