DP-300 practice questions 121–130

1. Question 121 · 354 in bank

   HOTSPOT - You have an Azure SQL logical server. You run the following script.

   

   For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   I think it's NO - as we are creating so called updatable ledger table (i.e. not append-only) , we can also delete and update rows. NO - the CREATE DATABASE statement does not contain "WITH LEDGER = ON" clause thus it can contain non-ledger tables NO - when the GENERATED ALWAYS columns are not specified in the CREATE statement they'll be created automatically

   Source: Adapted from community discussion — licna

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

2. Question 122 · 354 in bank

   You have an Azure SQL database named DB1. You need to encrypt DB1. The solution must meet the following requirements: • Encrypt data in motion. • Support comparison operators. • Provide randomized encryption. What should you include in the solution?

   • AAlways Encrypted with secure enclaves
   • BAlways Encrypted
   • Ccolumn-level encryption
   • DTransparent Data Encryption (TDE)

   Show answer

   Answer: A

   Explanation

   Always Encrypted with secure enclaves addresses these limitations by allowing some computations on plaintext data inside a secure enclave on the server side. A secure enclave is a protected region of memory within the Database Engine process. The secure enclave appears as an opaque box to the rest of the Database Engine and other processes on the hosting machine. There's no way to view any data or code inside the enclave from the outside, even with a debugger. These properties make the secure enclave a trusted execution environment that can safely access cryptographic keys and sensitive data in plaintext, without compromising data confidentiality.

   Source: Adapted from community discussion — Pranava_GCP

   References

   • Microsoft Learn

   Next question

3. Question 123 · 354 in bank

   You have an instance of SQL Server on Azure Virtual Machines. You need to ensure that a user named User1 can configure proxy accounts for SQL Server Agent jobs. The solution must use the principle of least privilege. Which role should you assign to User1?

   • Asysadmin
   • BSQLAgentUserRole
   • CSQLAgentReaderRole
   • DSQLAgentOperatorRole

   Show answer

   Answer: A

   Explanation

   The answer is correct. Only members of the sysadmin fixed server role have permission to create, modify, or delete proxy accounts. Users who are not members of the sysadmin fixed server role must be added to one of the following SQL Server Agent fixed database roles in the msdb database to use proxies: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole. https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver16#Permissions

   Source: Adapted from community discussion — U_C

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

4. Question 124 · 354 in bank

   Select 2

   You have an Azure SQL database named DB1. DB1 has a table named Table1 that contains the following columns.

   

   You plan to enable Always Encrypted for Table1. Which two columns support encryption? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

   • AColumn1
   • BColumn2
   • CColumn3
   • DColumn4
   • EColumn5

   Show answer

   Answer: DE

   Explanation

   Always Encrypted isn't supported for the columns using one of the following data types: xml, timestamp, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user-defined types.

   Source: Adapted from community discussion — kev22

   References

   • Microsoft Learn

   Next question

5. Question 125 · 354 in bank

   HOTSPOT - You have an Azure subscription that contains an Azure SQL managed instance named SQL1. You use the Microsoft Power BI service. You need to configure connectivity from the Power BI service to SQL1. The solution must ensure that only the Power BI service can initiate connections to SQL1. Which type of endpoint should you use for SQL1, and what should you use to control the connections to SQL1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   reference: https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-service-tags?source=recommendations The following configurations are necessary to successfully enable the endpoints for use in the Power BI service: 1. Enable a public endpoint in the SQL Managed Instance. 2. Create a Network Security Group rule to allow inbound traffic. 3. Enter the credentials in Power BI. So the answer is correct.

   Source: Adapted from community discussion — jerrychan

   References

   • Microsoft Learn

   

   Next question

6. Question 126 · 354 in bank

   HOTSPOT - You have an Azure subscription. You need to deploy a logical SQL server by using PowerShell. The solution must ensure that the logical SQL server can create Azure AD users and provide Transparent Data Encryption (TDE) with a customer-managed key. How should you complete the command? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   from URL, I see the first option should be "Assigned Identity". Answer should be "Assigned Identity" and "Key id" https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqlserver?view=azps-10.2.0#code-try-3

   Source: Adapted from community discussion — bsk1983

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

7. Question 127 · 354 in bank

   HOTSPOT - You have an Azure SQL database that contains a table named Customer. Customer has the columns shown in the following table.

   

   You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements: ✑ The first six numerals of each customer's phone number must be masked. ✑ The last four digits of each customer's phone number must be visible. ✑ Hyphens must be preserved and displayed. How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: 0 - Custom String : Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix Box 2: xxx-xxx - Box 3: 5 - Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

   Source: Adapted from community discussion — MustafaElmasry

   

   Next question

8. Question 128 · 354 in bank

   You have an Azure subscription. You create a logical SQL server that hosts four databases. Each database will be used by a separate customer. You need to ensure that each customer can access only its own database. The solution must minimize administrative effort. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • ADeny public access.
   • BCreate a private endpoint.
   • CCreate a database-level firewall rule.
   • DCreate a network security group (NSG).
   • ECreate a server-level firewall rule.

   Show answer

   Answer: C

   Explanation

   I see that most people voted for B and C. However, based on Microsfot documentation, you cannot create database-level firewall rule without configuring server-level firewall rule first: "You can only create and manage database-level IP firewall rules for master and user databases by using Transact-SQL statements and only after you configure the first server-level firewall." Any thoughts? Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql

   Source: Adapted from community discussion — d57ff1a

   References

   • Microsoft Learn

   Next question

9. Question 129 · 354 in bank

   DRAG DROP - You have an Azure key vault named Vault1 and a SQL server on Azure Virtual Machines instance named SQL1. SQL1 hosts a database named DB1. You need to configure Transparent Data Encryption (TDE) on DB1 to use a key in Vault1. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Provide some logic useing Chat: 1. Create an Azure AD service principal and grant the service principal permissions for Vault1: This step is necessary because SQL Server on an Azure VM will use this service principal to authenticate to Azure Key Vault and use the keys within it. 2. On SQL1, create a cryptographic provider and a Microsoft SQL Server credential: This step involves configuring SQL Server to be able to use Azure Key Vault as an Extensible Key Management (EKM) provider, which includes setting up the necessary credentials for authentication. 3. On SQL1, create an asymmetric key: This will be the key from the Azure Key Vault that you will use for TDE. SQL Server will use this key to encrypt the database encryption key. 4. On SQL1, create a login from the asymmetric key: This creates a SQL Server login that is based on the asymmetric key stored in Azure Key Vault, which is necessary for the SQL Server to use that key for TDE.

   Source: Adapted from community discussion — erz

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 130 · 354 in bank

    You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named VM1 and an Azure Active Directory Domain Services (Azure AD DS) domain that contains two users named User1 and User 2. On the default instance of SQL Server on VM1, you create a credential named Credential1 for User1. You need to ensure that User2 can create a SQL Server Agent proxy that will use Credential1. The solution must use the principle of least privilege. Which role should you assign to User2?

    • ASQLAgentUserRole
    • BSQLAgentReaderRole
    • CSQLAgentOperatorRole
    • Dsysadmin

    Show answer

    Answer: D

    Explanation

    The answer is correct. Based on the following link none of SQL Agent roles are not able to create proxies. So, user2 should have sysadmin permission. https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver16

    Source: Adapted from community discussion — Avacato

    References

    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 121. 354 questions available.