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
| Question | TDE | Cell-level encryption | Always Encrypted | Always Encrypted with secure enclaves |
|---|---|---|---|---|
| What is encrypted | The whole database: data files, log files, and backups | Selected values written to varbinary columns | Selected columns | Selected columns marked enclave-enabled |
| Where the keys live | DEK 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 database | CMK in an external key store; the database holds its location and the encrypted CEK | Same as Always Encrypted, with a CMK whose metadata declares ENCLAVE_COMPUTATIONS |
| Where data becomes plaintext | Database Engine memory, for every query | Database Engine, in a session that opened the key | Client driver only | Client driver and the secure enclave only |
| Hidden from database administrators | No | No, anyone with permissions on the key and certificate can decrypt | Yes, without key-store access | Yes, without key-store access |
| Server-side operations on encrypted values | All queries run unchanged | Queries call DECRYPTBYKEY | Equality only, with deterministic encryption | Comparisons, BETWEEN, and LIKE on randomized columns; in-place encryption |
| Application change | None | Queries call ENCRYPTBYKEY and DECRYPTBYKEY | Enabled driver; values sent as parameters | Enabled driver plus an attestation protocol setting |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
Also tested in
References
- https://learn.microsoft.com/en-us/training/modules/protect-data-transit-rest/2-explore-transparent-data-encryption
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
- https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data
- https://learn.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tde-certificate-migrate
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
- https://learn.microsoft.com/en-us/sql/t-sql/statements/open-symmetric-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbykey-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-master-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-master-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-encryption-key-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql
- https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/sqlclient-support-always-encrypted
- https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver
- https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver
- https://learn.microsoft.com/en-us/training/modules/protect-data-transit-rest/4-explain-object-encryption-secure-enclaves
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver17
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/migrate-sensitive-data-protected-by-always-encrypted
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
- https://learn.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-enclaves-enable
- https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database
- https://learn.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-database-firewall-rule-azure-sql-database
- https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-overview
- https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-dns
- https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture
- https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings
- https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8