DP-300 Practice Test 44 — 15 Questions

Question 1

You are implementing row-level security (RLS) in Azure SQL Database to restrict access to sales data based on the user's Azure AD identity. Which function should you use in the security policy?

Accepted Answer

USER_NAME(). In Azure SQL Database, row-level security (RLS) predicates must evaluate to a Boolean value based on the current user's identity. USER_NAME() returns the database principal name derived from the Azure AD token, which is the correct function to use when filtering rows by the user's Azure AD identity. The other functions either return the login name, the session user, or the current context user, which may not reflect the actual Azure AD user in an RLS predicate.

Answer

SESSION_USER()

Answer

SUSER_SNAME()

Answer

CURRENT_USER()

Question 2

Which TWO configurations are required to enable automatic failover for an Azure SQL Database configured with active geo-replication?

Accepted Answer

Configure a failover group that includes the primary and secondary databases.. A failover group is the Azure feature that manages automatic failover for geo-replicated databases. It coordinates the failover of the primary and all secondaries in the group, providing a single endpoint and automatic failover policy. Without a failover group, active geo-replication only supports manual failover.

Answer

Enable read-scale out on the secondary database.

Answer

Set the secondary database to have the same service tier as the primary.

Answer

Configure zone redundancy on the primary database.

Question 3

Your company uses Azure SQL Database for a customer-facing application. You need to ensure that only the application can access the database, and that access is restricted to specific IP ranges. The application runs on Azure App Service with a dynamic outbound IP address. What should you do?

Accepted Answer

Add a firewall rule for the App Service's outbound IP address range.. The application runs on Azure App Service with a dynamic outbound IP address, and the requirement is to restrict access to specific IP ranges. By adding a firewall rule for the App Service's outbound IP address range (which can be found in the App Service properties or via the `WEBSITE_OUTBOUND_IP_ADDRESSES` app setting), you allow the application to connect to Azure SQL Database while blocking other traffic. This approach directly addresses the dynamic IP challenge without requiring VNet integration or private endpoints.

Answer

Configure a VNet integration for the App Service and set the SQL firewall to allow the VNet subnet.

Answer

Migrate the database to Azure SQL Managed Instance and use a service endpoint.

Answer

Create a private endpoint for the SQL Database and connect App Service to it via VNet peering.

Question 4

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

Accepted Answer

The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address. When an Azure VM connects to Azure SQL Database, the source IP address seen by the SQL firewall is the VM's public outbound IP address due to Source Network Address Translation (SNAT) performed by Azure. Even if the VM is in the same region, traffic to Azure SQL Database egresses through the VM's public IP, not its private IP. Therefore, a firewall rule allowing the private IP will not match, causing the connection to fail.

Answer

The SQL Database has the public endpoint disabled

Answer

The SQL Database firewall is configured at the database level, not the server level

Answer

The VM does not have an outbound security rule allowing traffic to Azure SQL Database

Question 5

Your company uses Azure SQL Database. You need to ensure that all connections to the database use TLS 1.2 or higher. Currently, some client applications are connecting using TLS 1.0. What should you do?

Accepted Answer

Set the 'Minimum TLS version' property of the logical server to 1.2.. Setting the 'Minimum TLS version' property of the Azure SQL logical server to 1.2 enforces that all incoming connections must use TLS 1.2 or higher. This server-level setting overrides any client-side configuration, blocking connections that attempt to use TLS 1.0 or 1.1. It is the simplest and most effective way to enforce the minimum TLS version across all client applications without requiring changes to each client.

Answer

Configure the server firewall to block non-TLS traffic.

Answer

Set the 'tls_version' database parameter to 1.2 in the master database.

Answer

Update the client applications to only use TLS 1.2.

Question 6

You manage an Azure SQL Database that uses elastic jobs to automate index maintenance. The job fails intermittently with the error 'Cannot open server 'myserver' requested by the login.' The job agent uses a database-scoped credential. What is the most likely cause?

Accepted Answer

The credential is not mapped to a server login in the master database.. The error 'Cannot open server...' indicates that the login used by the credential is not recognized at the server level. For elastic jobs with database-scoped credentials, the credential must be associated with a login in the master database of the target server. If the credential is not mapped to a server login, the login cannot access the server, causing this error. Option D correctly identifies this. Option A is incorrect because ALTER ANY DATABASE permission is not required for server access. Option B is incorrect because firewall blocks cause a different error (e.g., connection timeout). Option C is incorrect because the job agent itself does not require a server-level login; it uses the credential.

Answer

The credential's user does not have ALTER ANY DATABASE permission.

Answer

The target database's firewall is blocking the job agent's IP address.

Answer

The job agent itself needs a server-level login.

Question 7

Which TWO Azure services can be used to automate the deployment of schema changes to Azure SQL Database?

Accepted Answer

Azure DevOps Services. Options D and E are correct. Azure DevOps Services provides pipeline capabilities for CI/CD to automate deployment of schema changes. Azure Automation can be used with runbooks to execute T-SQL scripts on a schedule or event trigger. Option A (Azure Portal) is a manual interface. Option B (SSMS) is a manual tool. Option C (Elastic Database Jobs) is for ad-hoc execution, not a full deployment pipeline.

Answer

Azure Portal

Answer

SQL Server Management Studio (SSMS)

Answer

Elastic Database Jobs

Question 8

You are the database administrator for a healthcare company that uses Azure SQL Database to store patient records. The database is named PatientDB. The security team mandates that all database access must be audited, and any suspicious activity must be alerted in real-time. Additionally, compliance requires that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. You have configured the following:
- TDE with customer-managed key in AKV (key vault name: KV-Health, key name: PatientKey)
- Azure SQL Auditing enabled, writing logs to a storage account (StorageAcctLogs)
- Advanced Threat Protection (ATP) enabled with alerts sent to the security team's email
- Firewall rules allowing only the application server's public IP (203.0.113.50)

A week later, the security team reports that they received an ATP alert about a potential SQL injection attack from IP 198.51.100.25. However, when they check the audit logs, they find no entries from that IP. They also notice that the database remains accessible. The security team wants to know why the audit logs do not contain the suspicious IP even though ATP detected it. What is the most likely reason?

Accepted Answer

The firewall rule blocks connections from the attacker's IP, so no audit log entries are generated because the connection never reaches the database.. Advanced Threat Protection (ATP) can detect suspicious activity at the network level before the connection is fully established, while Azure SQL Auditing only logs events after a connection is accepted and authenticated. Since the firewall rule blocks connections from IP 198.51.100.25, the attacker's IP never reaches the database engine, so no audit log entries are generated for that IP. ATP alerts can be triggered by network-level patterns (e.g., SQL injection signatures) even when the connection is denied by the firewall, explaining the discrepancy.

Answer

The storage account for audit logs is not accessible, so logs are not being written.

Answer

The firewall rule allowing only the application server's IP is misconfigured, allowing the attacker's IP.

Answer

The TDE configuration with customer-managed key is interfering with auditing.

Question 9

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

Accepted Answer

Azure Defender for SQL and SQL Auditing. Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Answer

Azure Purview and vulnerability assessment

Answer

Microsoft Sentinel and SQL Auditing

Answer

SQL Server auditing and vulnerability assessment

Question 10

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a regional outage, you fail over to the secondary region. When the primary region recovers, you need to fail back with zero data loss. What should you do?

Accepted Answer

Perform a planned failover from the current primary to the original primary. A planned failover in Azure SQL Database with Active Geo-Replication ensures zero data loss by synchronizing all committed transactions from the current primary (secondary region) to the original primary before switching roles. This process uses synchronous replication during the final phase, guaranteeing that no transactions are lost when failing back to the recovered primary region.

Answer

Delete the secondary database and recreate it from the primary

Answer

Add a new secondary in the original primary region and then fail over

Answer

Fail over the original primary to become the primary again

Question 11

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Accepted Answer

Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.. Creating a plan guide for the previous plan manually enforces that plan for the query, and disabling the automatic tuning recommendation for that specific query prevents the automatic tuning engine from forcing the same plan again. Option A is incorrect because reindexing may not resolve the regression and does not stop automatic tuning from forcing the bad plan. Option C is incorrect because disabling automatic tuning globally affects all queries unnecessarily. Option D is incorrect because clearing the plan cache is only temporary and automatic tuning would likely force the same plan again.

Answer

Reindex the tables involved in the query.

Answer

Disable automatic tuning for the database.

Answer

Run DBCC FREEPROCCACHE to clear the plan cache.

Question 12

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

Accepted Answer

The OrdersDB database's service objective is updated to S2.. The 'az sql db update' command updates an existing database's service objective to S2. This immediate effect changes the performance level of the OrdersDB database. Option A is incorrect because the command targets the database, not the server. Option C is incorrect because the command does not specify scaling direction; it simply sets the service objective, which could be an upgrade or downgrade depending on the current setting. Option D is incorrect because the update command does not create a new database; it modifies the existing one.

Answer

The server's performance tier is changed to S2.

Answer

The database is scaled down from a higher tier to S2.

Answer

A new database named OrdersDB is created with S2 tier.

Question 13

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

Accepted Answer

Schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION to SPLIT the range and MERGE the oldest boundary.. To automate monthly partition maintenance, you need to add a new partition for the next month (split the range) and remove the oldest partition (merge the oldest boundary). The T-SQL commands ALTER PARTITION FUNCTION ... SPLIT RANGE and ALTER PARTITION FUNCTION ... MERGE RANGE accomplish this. Scheduling these via Elastic Database Jobs provides automation. Option A's SELECT INTO and TRUNCATE are not partition management operations. Option B's ALTER INDEX REORGANIZE is for index maintenance, not partition boundary changes. Option D's SWITCH PARTITION moves data without altering the partition function/scheme.

Answer

Create a stored procedure that uses SELECT INTO to copy old data to a history table and then TRUNCATE the partition.

Answer

Use ALTER INDEX REORGANIZE to compress old partitions.

Answer

Use the SWITCH PARTITION operation to move the oldest partition to a staging table and then drop the staging table.

Question 14

You are responsible for automating index maintenance and statistics updates across multiple Azure SQL Databases in an elastic pool. Each database has varying workloads and sizes. The automation must run weekly during low-traffic periods and should not degrade performance for critical applications. Which three actions should you take to implement a reliable and efficient automation solution?

Accepted Answer

Create an Azure Automation account and schedule a PowerShell runbook that connects to each database and runs index maintenance scripts.. Azure Automation with PowerShell runbooks allows scheduling and executing T-SQL scripts across databases. Option C is correct because Elastic Database Jobs can target a group of databases in an elastic pool. Option D is correct because automatic tuning can handle index and statistics maintenance, reducing manual effort. Option B is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance) and cannot be used across multiple databases in an elastic pool. Option E is incorrect because auto-failover groups are for high availability and disaster recovery, not for automating maintenance tasks.

Answer

Configure SQL Agent jobs in each database to run the maintenance scripts.

Answer

Enable auto-failover groups for all databases to automate failover during maintenance.

Question 15

Your Azure SQL Database is configured with a failover group across two regions. You need to ensure that client connections automatically redirect to the secondary region during a regional outage, without changing the connection string. What should you implement?

Accepted Answer

Configure the failover group with a listener endpoint and use that in the connection string.. A failover group in Azure SQL Database provides a listener endpoint (e.g., `server.database.windows.net`) that automatically resolves to the primary or secondary region based on the current role. By using this listener in the connection string, client connections are redirected transparently during a regional outage without any manual changes to the connection string, as the DNS record is updated by the failover group to point to the secondary region.

Answer

Enable read scale-out and configure application retry logic.

Answer

Deploy Azure Traffic Manager in front of the SQL Database and configure failover routing.

Answer

Use private endpoints in both regions and configure DNS with a CNAME record.