Memory hook: Authentication identifies; permissions authorise; encryption and masking protect different exposures.
Must remember
- Configure Entra or SQL authentication as supported. Logins, database users, roles and object permissions have different scopes. Use least privilege through T-SQL or supported tools; distinguish failure to authenticate from successful login with insufficient database rights.
- TDE protects supported database files/backups at rest; TLS protects connections; object-level encryption and Always Encrypted address different threat models. Always Encrypted keeps selected data encryption under client control; secure enclaves enable supported confidential computations with additional requirements.
- Firewall rules, service endpoints and private links restrict access paths. A permitted network connection still needs database authentication and permissions. Key Vault/key rotation and recovery must preserve the ability to decrypt retained data.
- Dynamic data masking changes how selected users see results; it is not a robust boundary against a principal able to infer/query underlying data broadly. Row-level security filters accessible rows using defined policy logic; test administrative and application contexts.
- Classification labels identify sensitive data; audits record configured operations. Change tracking/CDC serve change-consumption purposes and are not identical to security audit. Ledger adds tamper-evidence capabilities; it does not replace backup or prove every business input was truthful.
- Review access, audit retention and export destinations, privileged identities and incident procedures. A data breach investigation needs identity, query and configuration context, not only a screenshot of enabled encryption.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Protect database files at rest | TDE with sound key management. |
| Keep selected plaintext from the database service boundary | Evaluate Always Encrypted and application compatibility. |
| Different users may access different rows | Row-level security with tested policy logic. |
Traps
- Masking is not encryption.
- TDE does not prevent an authorised query from returning plaintext.
- Network allow rules do not grant SQL permissions.
Active recall
1. What distinguishes a login from a database user?
They participate at different authentication/database authorisation scopes, according to the platform.
2. Why preserve old encryption-key access?
Retained backups/data may require it for recovery.
3. Can an authorised administrator query TDE-protected data?
Yes, if database permissions permit; TDE primarily protects stored files.
4. What is ledger evidence for?
Detecting supported tampering/history changes, not guaranteeing that entered facts were correct.
5. Why test row-level security under several identities?
Policy behaviour depends on execution context and can unintentionally expose or hide rows.