Domain 2 of 5 · Chapter 2 of 3

Implement security for data at rest and data in transit

Unlock the complete study guide + 25 practice exams totaling 1,500 questions.

Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.

14-day refund guarantee on one-time purchases and your first subscription charge.

Included in this chapter:

  • Where plaintext appears: one model for this objective
  • TDE: what it encrypts and what it leaves alone
  • Certificate-based TDE on SQL Server
  • Turning TDE on in Azure and confirming it finished
  • Customer-managed TDE protectors in Key Vault
  • Rotating the TDE protector without stranding backups
  • Cell-level encryption with symmetric keys
  • Moving a database that uses a database master key
  • Always Encrypted: keys stay outside the database
  • Choosing an encryption type for each encrypted column
  • Querying Always Encrypted columns from applications
  • Encrypting existing columns without an enclave
  • Bulk copying encrypted data with ALLOW_ENCRYPTED_VALUE_MODIFICATIONS
  • Always Encrypted with secure enclaves
  • Choosing and enabling an enclave technology
  • IP firewall rules at the server and database level
  • Virtual network rules with service endpoints
  • Private endpoints for Azure SQL Database
  • TLS: encrypt the connection and validate the server
  • Matching requirements to Azure SQL security controls

Where plaintext appears: TDE vs cell-level encryption vs Always Encrypted vs Always Encrypted with secure enclaves

QuestionTDECell-level encryptionAlways EncryptedAlways Encrypted with secure enclaves
What is encryptedThe whole database: data files, log files, and backupsSelected values written to varbinary columnsSelected columnsSelected columns marked enclave-enabled
Where the keys liveDEK in the user database, protected by the TDE protector (service-managed certificate or a Key Vault or Managed HSM key)Symmetric key in the user database, protected by a certificate in that databaseCMK in an external key store; the database holds its location and the encrypted CEKSame as Always Encrypted, with a CMK whose metadata declares ENCLAVE_COMPUTATIONS
Where data becomes plaintextDatabase Engine memory, for every queryDatabase Engine, in a session that opened the keyClient driver onlyClient driver and the secure enclave only
Hidden from database administratorsNoNo, anyone with permissions on the key and certificate can decryptYes, without key-store accessYes, without key-store access
Server-side operations on encrypted valuesAll queries run unchangedQueries call DECRYPTBYKEYEquality only, with deterministic encryptionComparisons, BETWEEN, and LIKE on randomized columns; in-place encryption
Application changeNoneQueries call ENCRYPTBYKEY and DECRYPTBYKEYEnabled driver; values sent as parametersEnabled driver plus an attestation protocol setting

Decision tree

Protect stored filesand backups, notselected columns?YesTDEon by defaultNoMust administratorsbe kept fromplaintext?NoCell-levelencryptionYesNeed range or LIKEqueries, or in-placeencryption?NoAlwaysEncryptedYesAlways Encrypted with secure enclavesAlways: TLS on every connection;network rules decide who connects

Cheat sheet

  • TDE encrypts database storage without changing ordinary application queries
  • SQL Server TDE recovery depends on the certificate protecting the database encryption key
  • TDE configuration and completed encryption are different operational states
  • Customer-managed TDE delegates protector lifecycle control without replacing page encryption
  • Customer-managed TDE depends on durable key access for the SQL resource's managed identity
  • Rotating a TDE protector does not re-key older backups
  • Cell-level encryption protects selected values through keys in the user database
  • Symmetric-key decryption requires an open key in the correct database context
  • A stable authenticator helps detect encrypted values moved between rows
  • Moving a SQL Server database can break automatic opening of its database master key
  • Database-specific IP access requires a rule stored in the target database
  • A narrow database firewall rule does not cancel a broader server allowance
  • Allow Azure services admits Azure-origin traffic beyond the owning subscription
  • Always Encrypted separates database administration from permission to see sensitive values
  • Always Encrypted stores key references and wrapped keys in SQL rather than plaintext master keys
  • Without enclaves, deterministic encryption enables equality operations while randomized encryption limits leakage
  • Always Encrypted query access spans the database, client driver, and key store
  • Always Encrypted values must reach SQL as correctly encrypted parameters
  • Conventional Always Encrypted conversion requires client-side cryptographic tooling
  • Always Encrypted column eligibility constrains the schema before encryption can begin
  • Secure enclaves enable richer comparisons without exposing plaintext to the surrounding engine
  • Enclave-enabled keys allow column cryptographic changes to remain inside the database
  • Enclave technology determines the supported attestation path
  • VBS enclave enablement is a database or elastic-pool configuration rather than a query switch
  • Private endpoints change SQL network resolution without changing the logical server connection name
  • Private endpoint admission and public endpoint shutdown are separate network decisions
  • SQL service endpoints authorize a subnet without allocating a private SQL address
  • Encrypted SQL connections should also validate the server certificate

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. https://learn.microsoft.com/en-us/training/modules/protect-data-transit-rest/2-explore-transparent-data-encryption
  2. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview
  5. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data
  6. https://learn.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview
  7. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  8. https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql
  9. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tde-certificate-migrate
  10. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql
  11. https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-overview
  12. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
  13. https://learn.microsoft.com/en-us/sql/t-sql/statements/open-symmetric-key-transact-sql
  14. https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql
  15. https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbykey-transact-sql
  16. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql
  17. https://learn.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql
  18. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-master-key-transact-sql
  19. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-master-key-transact-sql
  20. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-encryption-key-transact-sql
  21. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql
  22. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/sqlclient-support-always-encrypted
  23. https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver
  24. https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver
  25. https://learn.microsoft.com/en-us/training/modules/protect-data-transit-rest/4-explain-object-encryption-secure-enclaves
  26. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver17
  27. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/migrate-sensitive-data-protected-by-always-encrypted
  28. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves
  29. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
  30. https://learn.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-enclaves-enable
  31. https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
  32. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database
  33. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database
  34. https://learn.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview
  35. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-database-firewall-rule-azure-sql-database
  36. https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-overview
  37. https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-dns
  38. https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture
  39. https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings
  40. https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8