Table of Contents
Which SQL Security Feature Prevents Database Admins From Viewing Sensitive Data?
Discover why Always Encrypted is the only SQL security option that ensures database admins cannot see sensitive data like credit card information. A critical concept for the AZ-500 exam, this feature separates data ownership from data management.
Question
You need to implement security in SQL server to ensure database admins never see sensitive customer financial information, such as credit card data, in databases they manage. Which SQL data security option should you choose?
A. Row-level security
B. Always Encrypted
C. Transparent Data Encryption
D. Dynamic Data Masking
Answer
B. Always Encrypted
Explanation
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data and manage it.
The correct SQL data security option to prevent database admins from ever seeing sensitive customer financial information is Always Encrypted. This feature is specifically designed to protect sensitive data from high-privileged but non-authorized users, such as database administrators.
Always Encrypted is a client-side encryption technology that ensures sensitive data is always encrypted, both in transit and at rest. The encryption and decryption of data occur entirely within the client application or a client-side driver, such as the .NET Framework Data Provider for SQL Server. The actual encryption keys are never revealed to the Database Engine.
This model creates a complete separation between those who own and can view the data (the application users) and those who manage the data but should not see it (the database administrators). A DBA can perform administrative tasks on the database, but when they query a table containing an encrypted column, they will only see the encrypted ciphertext. Because the Database Engine never has access to the Column Master Key (CMK), which is stored securely on the client-side (e.g., in Azure Key Vault or the Windows Certificate Store), it is impossible for the DBA to decrypt the data.
Incorrect Options Explained
Row-Level Security (RLS): This feature controls data access at the table row level. It works by applying a security predicate that filters which rows a user is allowed to view or modify based on their execution context. RLS does not encrypt data; it only limits which rows are returned in a query. A database admin could potentially alter or disable the RLS policy and still view the sensitive data in its unencrypted form.
Transparent Data Encryption (TDE): TDE provides encryption-at-rest for the entire database, including its data files, log files, and backups. This protects the data if the physical storage media is stolen. However, the decryption is transparent to authenticated users with access to the database. A database admin with sufficient permissions can query the database and view all data in its decrypted state, as the database engine handles decryption automatically when data is read from disk.
Dynamic Data Masking (DDM): DDM limits sensitive data exposure by masking it in query results for non-privileged users. For example, it might show a credit card number as xxxx-xxxx-xxxx-1234. DDM does not actually encrypt or alter the data stored in the database. A user with high privileges, such as a database admin, can be granted UNMASK permissions, allowing them to bypass the masking and view the original, sensitive data.
Microsoft Certified Azure Security Engineer Associate AZ-500 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft Certified Azure Security Engineer Associate AZ-500 exam and earn Microsoft Certified Azure Security Engineer Associate AZ-500 certification.