Q01 - Question
Your company uses Azure SQL Database. A compliance team must manage security policies for the databases and servers, such as auditing. The team must not be able to connect to the databases or read data. You want to avoid assigning the higher-level Owner or Contributor roles. Which Azure built-in role should you assign to the compliance team?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Owner
- B. SQL DB Contributor
- C. SQL Security Manager
- D. SQL Server Contributor
C is correct.
Explanation: SQL Security Manager can manage security policies for databases and instances, such as auditing, but can't access them. This separates security management from data access and avoids the broader Owner and Contributor roles.
A is incorrect: Owner is one of the higher-level RBAC roles that the built-in Azure SQL roles are intended to reduce the need for. Assign a more specific built-in role instead.
B is incorrect: SQL DB Contributor can create and manage databases but can't access them. The documented purpose of this role is database management, not security policy management.
D is incorrect: SQL Server Contributor can manage servers and databases but can't access them. The documented purpose of this role is server and database management, not security policy management.
Q02 - Question
You manage permissions for a user database in Azure SQL Database. Several Microsoft Entra users need the same read access to the Sales schema. Select two actions that you can perform to meet this requirement.
Domain: Implement a secure environment (20–25%) Type: Multi-select
- A. Create a custom server role, and grant it access to the Sales schema.
- B. Run CREATE USER with FROM EXTERNAL PROVIDER in the user database for each Microsoft Entra user.
- C. Add the users to the db_backupoperator role to grant access to the Sales schema.
- D. Create a database role, grant it SELECT on the Sales schema, and add the users as members.
B and D are correct.
Explanation: Running CREATE USER with FROM EXTERNAL PROVIDER in the user database creates a contained Microsoft Entra user. This is the recommended scope for Azure SQL Database users. A database role with SELECT granted on the Sales schema lets members inherit the permission, so you manage the permission once for all users.
A is incorrect: New server roles can't be created in Azure SQL Database. Server roles also can't be granted access to objects within a database directly.
C is incorrect: The db_backupoperator role is for backing up a database. It doesn't confer any permissions in Azure SQL Database, so it doesn't grant access to the Sales schema.
Q03 - Question
A Sales table in Azure SQL Database stores rows for several tenants in a TenantName column. Each tenant user connects directly to the database and must see only the rows for their own tenant. A TenantAdmin user must see all rows. The solution must require minimal application changes. Which approach should you use?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Apply a Dynamic Data Masking default() function to the TenantName column.
- B. Enable Azure SQL auditing with the BATCH_COMPLETED_GROUP action group.
- C. Create an inline table-valued function that evaluates the user name, and add it as a filter predicate in a security policy on the Sales table.
- D. Add sensitivity labels to the TenantName column by using Data Discovery & Classification.
C is correct.
Explanation: Row-level security uses a security policy that invokes an inline table-valued function as a predicate. The predicate compares the user name with the TenantName value and allows TenantAdmin to see all rows. The control is applied at the database level, so application changes are minimal. Users can query the table directly and see only their own rows. Limit the ability of users to run unplanned queries, because a specially crafted WHERE clause can cause information leakage through a side-channel attack.
A is incorrect: Dynamic Data Masking obfuscates column values. It does not remove rows from query results.
B is incorrect: Auditing records events such as queries. It does not restrict which rows a user can read.
D is incorrect: Sensitivity labels describe the sensitivity of columns. They do not enforce access to rows.
Q04 - Question
Auditing is enabled at the server level on an Azure SQL logical server, and all databases write audit logs to one Log Analytics workspace. A compliance requirement now states that one database must send its audit logs to a different storage account and use a different retention period. The other databases must keep the current configuration. What should you do?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Disable server-level auditing and enable database-level auditing on every database.
- B. Keep only server-level auditing and leave database-level auditing disabled on the database.
- C. Disable database-level auditing on all databases and rely on the data_sensitivity_information field in the audit log.
- D. Keep server-level auditing and also enable database-level auditing on that database with its own storage account and retention period.
D is correct.
Explanation: Enabling server auditing and database auditing together is appropriate when a specific database needs a different storage account, retention period, or Log Analytics workspace. The server policy continues to audit the other databases. The database policy applies the separate settings to the one database. Both audits exist at the same time.
A is incorrect: Server policies automatically cover new and existing databases. Moving every database to database-level auditing adds configuration work and is not needed for one exception.
B is incorrect: Server-level auditing alone is recommended for databases that have no special requirements. This database needs a different destination and retention period, so it needs its own database-level policy.
C is incorrect: The data_sensitivity_information field records sensitivity labels of the data a query returns. It does not change the audit destination or the retention period.
Q05 - Question
During heavy workloads, an application that connects to Azure SQL Database occasionally loses its connection and receives the error "Can't process request. Not enough resources to process request." The failures last only a few seconds. You must reduce the number of errors shown to users. Which approach should you implement in the application?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Retry immediately in a loop with no maximum number of retries.
- B. Implement retry logic that waits at least 5 seconds before the first retry, increases the delay exponentially up to 60 seconds, and sets a maximum number of retries.
- C. Display the error to users and require them to reconnect manually.
- D. File an Azure support request each time the error occurs.
B is correct.
Explanation: Transient faults occur during database reconfiguration and are brief, usually under 60 seconds. Applications should implement retry logic instead of showing errors to users. Wait at least 5 seconds before the first retry, increase each later delay exponentially up to 60 seconds, and set a maximum number of retries before the program terminates.
A is incorrect: Immediate retries without a limit don't follow the recommended delay, and the application never terminates after repeated failures.
C is incorrect: Showing application errors to users is what retry logic is meant to avoid. The failures are transient and brief.
D is incorrect: File a support request when the connectivity error lasts longer than 60 seconds or occurs more than once in a given day. A brief transient error doesn't meet that condition.
Q06 - Question
You manage an Azure SQL Database. A table contains a column named column1 that stores customer email addresses. You review the classification recommendations in Data Discovery & Classification, and column1 is not recommended as sensitive personal information. You must make sure the column is classified so that audits can identify it as sensitive. What should you do?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Wait for the classification engine to rescan the database and recommend the column.
- B. Classify the column manually by using the ADD SENSITIVITY CLASSIFICATION T-SQL command or the sensitivity wizard in SQL Server Management Studio.
- C. Configure a Dynamic Data Masking rule on the column to classify it as sensitive.
- D. Create a row-level security policy on the table to mark the column as sensitive.
B is correct.
Explanation: The classification engine bases its recommendations on column names. A column named column1 is not recommended, even when it contains email addresses. You can classify the column yourself by using the sensitivity wizard in SQL Server Management Studio or the ADD SENSITIVITY CLASSIFICATION T-SQL command with a label and an information type.
A is incorrect: A rescan does not change the result. The recommendations depend on the column name, not on the column contents.
C is incorrect: Dynamic Data Masking obfuscates data for users who do not need to see it. It does not assign a sensitivity label or an information type.
D is incorrect: Row-level security restricts which rows a user can access. It does not classify columns.
Q07 - Question
An application accesses all of its data in an Azure SQL database by calling stored procedures. The procedures and the tables they reference have the same owner, and the procedures don't use dynamic SQL. You must follow the principle of least privilege for the application account. What should you grant to the application account?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. SELECT, INSERT, UPDATE, and DELETE on every table the procedures use
- B. Membership in the db_datareader and db_datawriter roles
- C. CONTROL on each table the procedures use
- D. EXECUTE on the stored procedures, with no direct access to the tables
D is correct.
Explanation: The principle of least privilege means giving an application only the permissions it needs. If an application accesses all data through stored procedures, it needs only EXECUTE on those procedures. Because the procedures and tables have the same owner and the procedures don't use dynamic SQL, the ownership chain lets the procedures access the tables.
A is incorrect: Direct DML permissions on the tables give the application more access than it needs, because the procedures already reach the tables through the ownership chain.
B is incorrect: db_datareader reads every table and view, and db_datawriter changes data in every table and view. Both are broader than needed.
C is incorrect: CONTROL grants all rights to an object, including the ability to delete it. This is the opposite of least privilege.
Q08 - Question
You plan to create a new Azure SQL database for a system of record. Auditors require cryptographic proof that data was not tampered with, and all future tables in the database must be ledger tables by default. When and how should you configure ledger?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. During database creation, select Ledger on the Security tab in the Azure portal, select Configure ledger, and select Enable for all future tables in this database.
- B. After database creation, enable ledger from the Auditing page of the server.
- C. After database creation, enable ledger from Data Discovery & Classification.
- D. After database creation, enable ledger by turning on Microsoft Defender for SQL.
A is correct.
Explanation: You can enable the ledger capability only when you create the database. You cannot modify it afterward. Selecting Enable for all future tables in this database makes new tables updatable ledger tables by default, even when CREATE TABLE does not specify LEDGER = ON. You must also provide storage for the database digests.
B is incorrect: Auditing records database events. It does not enable ledger, and ledger cannot be enabled after the database is created.
C is incorrect: Data Discovery & Classification manages sensitivity labels and information types. It does not enable ledger.
D is incorrect: Microsoft Defender for SQL provides vulnerability assessment and Advanced Threat Protection. It does not enable ledger.
Q09 - Question
Your company runs a database in Azure SQL Database. The security team must control the TDE protector, separate key management from data management, and be able to make the encrypted database inaccessible by revoking key access. Which configuration meets these requirements?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Transparent Data Encryption with a customer-managed key stored in Azure Key Vault
- B. Transparent Data Encryption with the default Microsoft-provided certificate (service-managed key)
- C. Transport Layer Security (TLS) encrypted connections for all client applications
- D. A database-level firewall rule that allows only the application server IP address
A is correct.
Explanation: A customer-managed key (Bring Your Own Key) stored in Azure Key Vault gives you control over usage and management of the TDE protector. It lets you separate key management from data management. The key vault administrator can revoke key access permissions to make the encrypted database inaccessible.
B is incorrect: The default configuration uses a Microsoft-provided certificate (service-managed key). Your company does not control the TDE protector with this option.
C is incorrect: TLS encrypts data in transit between the client and the server. It does not control the TDE protector or revoke access to data at rest.
D is incorrect: A firewall rule restricts which IP addresses can connect. It does not manage encryption keys or make the encrypted database inaccessible.
Q10 - Question
A user is a member of the SalesReader role, which has SELECT and EXECUTE on the Sales schema and no permissions on the Production schema. The user runs Sales.DemoProc, which queries Production.Product. The procedure and table have the same owner, and the procedure runs successfully. A developer then changes the procedure to build the same query as a string and run it with sp_executesql. The user now receives an error that SELECT permission is denied on Production.Product. What is the cause?
Domain: Implement a secure environment (20–25%) Type: Single choice
- A. Dynamic SQL runs outside the context of the calling stored procedure, so the ownership chain doesn't apply.
- B. The user lost EXECUTE permission on the Sales schema when the procedure was altered.
- C. The SalesReader role must be a member of db_owner to run procedures that use sp_executesql.
- D. The user has an implicit DENY on the Production schema because the user belongs to the public role.
A is correct.
Explanation: Ownership chaining lets a user run a procedure that accesses a table with the same owner without direct table permissions. Dynamic SQL runs outside the context of the calling procedure, which breaks the chain. The user running the dynamic SQL then needs permissions on the tables and views that the code uses.
B is incorrect: The user's EXECUTE permission comes from the role's grant on the Sales schema. The error is about SELECT on Production.Product, not EXECUTE on the procedure.
C is incorrect: Adding the role to db_owner would grant broad administrative access and doesn't address the broken chain. It also conflicts with the principle of least privilege.
D is incorrect: Membership in the public role doesn't create a DENY. By default, the public role has no permissions granted to it.