A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?
Trap 1: Dynamic Data Masking
Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.
Trap 2: Transparent Data Encryption
Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.
Trap 3: Row-Level Security
Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.
- A
Dynamic Data Masking
Why wrong: Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.
- B
Always Encrypted
Always Encrypted encrypts data on the client side, so the database never sees plaintext. DBAs cannot access the encryption keys and therefore cannot view the sensitive data.
- C
Transparent Data Encryption
Why wrong: Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.
- D
Row-Level Security
Why wrong: Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.