DP-300 practice questions 101–110

1. Question 101 · 354 in bank

   You have an Azure subscription that contains a server named Server1. Server1 hosts two Azure SQL databases named DB1 and DB2. You plan to deploy a Windows app named App1 that will authenticate to DB2 by using SQL authentication. You need to ensure that App1 can access DB2. The solution must meet the following requirements: ✑ App1 must be able to view only DB2. ✑ Administrative effort must be minimized. What should you create?

   • Aa contained database user for App1 on DB2
   • Ba login for App1 on Server1
   • Ca contained database user from an external provider for App1 on DB2
   • Da contained database user from a Windows login for App1 on DB2

   Show answer

   Answer: A

   Explanation

   The question specify "by using SQL authentication". So is NOT External Provider or Windows login, but only Contained Database use.

   Source: Adapted from community discussion — Ciupaz

   Next question

2. Question 102 · 354 in bank

   You create five Azure SQL Database instances on the same logical server. In each database, you create a user for an Azure Active Directory (Azure AD) user named User1. User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error. You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases. What should you do?

   • ACreate User1 in the master database.
   • BAssign User1 the db_datareader role for the master database.
   • CAssign User1 the db_datareader role for the databases that User1 creates.
   • DGrant SELECT on sys.databases to public in the master database.

   Show answer

   Answer: A

   Explanation

   Answer is correct knowing that granting select permissions to the public role in the master database will not impact User1, since this user is contained. The public role in Azure SQL Database only exists at the database-level.

   Source: Adapted from community discussion — Behto

   Next question

3. Question 103 · 354 in bank

   You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked. You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1. You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements: ✑ Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1. ✑ Restrict network connectivity to SqlSrv1. What should you create on VNet1?

   • Aa VPN gateway
   • Ba service endpoint
   • Ca private endpoint
   • Dan ExpressRoute gateway

   Show answer

   Answer: C

   Explanation

   A private endpoint is a network interface that uses a private IP address from your virtual network. This network interface connects you privately and securely to a service powered by Azure Private Link. By enabling a private endpoint, you're bringing the service into your virtual network. The service could be an Azure service such as: ✑ Azure Storage ✑ Azure Cosmos DB ✑ Azure SQL Database ✑ Your own service using a Private Link Service. Reference: https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview

   Source: Adapted from community discussion — Raffer

   References

   • Microsoft Learn

   Next question

4. Question 104 · 354 in bank

   HOTSPOT - You have an Azure SQL database named db1 that contains an Azure Active Directory (Azure AD) user named user1. You need to test impersonation of user1 in db1 by running a SELECT statement and returning to the original execution context. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. 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: USER - Using EXECUTE AS and REVERT to switch context. The following example creates a context execution stack using multiple principals. The REVERT statement is then used to reset the execution context to the previous caller. ** EXECUTE AS USER = 'user2'; --The following REVERT statements will reset the execution context to the previous context. REVERT; ** Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/suser-sname-transact-sql?view=sql-server-ver15

   

   Next question

5. Question 105 · 354 in bank

   You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked. You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1. You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements: ✑ Ensure that all traffic to the public endpoint of SqlSrv1 is blocked. ✑ Minimize the possibility of VM1 exfiltrating data stored in SqlDb1. What should you create on VNet1?

   • Aa VPN gateway
   • Ba service endpoint
   • Ca private link
   • Dan ExpressRoute gateway

   Show answer

   Answer: C

   Explanation

   corrected "With Service Endpoints, traffic still left you vNet and hit the public endpoint of the PaaS resource, with Private Link the PaaS resource sits within your vNet and gets a private IP on your vNet. When you send traffic to the PaaS resource, it does not leave the virtual network." https://samcogan.com/service-endpoints-and-private-link-whats-the-difference/#:~:text=The%20key%20difference%20between%20Private,resource%20into%20your%20virtual%20network.&text=Unlike%20Service%20Endpoints%2C%20Private%20Link,ExpressRoute%2C%20and%20from%20peered%20networks.

   Source: Adapted from community discussion — Phund

   Next question

6. Question 106 · 354 in bank

   DRAG DROP - You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1. You need to encrypt the data in Col1. Which four actions should you perform for DB1 in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place:

   

   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

   Use the following steps for column level encryption: 1. Create a database master key (Step 1) 2. Create a self-signed certificate for SQL Server (Step 2) 3. Configure a symmetric key for encryption (Step 3) 4. Encrypt the column data (this includes Open the symmetric key - Step 4) 5. Query and verify the encryption Step 1: Create a database master key Create a database master key for column level SQL Server encryption In this first step, we define a database master key and provide a password to protect it. It is a symmetric key for protecting the private keys and asymmetric keys. Step 2: Create a certificate. Create a self-signed certificate for Column level SQL Server encryption In this step, we create a self-signed certificate using the CREATE CERTIFICATE statement. You might have seen that an organization receives a certificate from a certification authority and incorporates into their infrastructures. In SQL Server, we can use a self-signed certificate without using a certification authority certificate. Step 3: Create a symmetric key. Configure a symmetric key for column level SQL Server encryption. In this step, we will define a symmetric key that you can see in the encryption hierarchy as well. The symmetric key uses a single key for encryption and decryption as well. Step 4: Open the symmetric key - Data encryption. Let's encrypt the data in this newly added column. In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier Etc. Reference: https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

   Source: Adapted from community discussion — GeoFlux121

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

7. Question 107 · 354 in bank

   HOTSPOT - You have a Microsoft SQL Server database named DB1 that contains a table named Table1. The database role membership for a user named User1 is shown in the following exhibit.

   

   Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. 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: delete a row from Table1 - Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. Box 2: db_datareader - Members of the db_datareader fixed database role can read all data from all user tables. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

   Source: Adapted from community discussion — igorclapa

   References

   • Microsoft Learn

   

   Next question

8. Question 108 · 354 in bank

   You have an Azure subscription that contains a logical SQL server named Server1. The master database of Server1 contains a user named User1. You need to ensure that User1 can create databases on Server1. Which database role should you assign to User1?

   • Adb_owner
   • Bdbmanager
   • Cdbo
   • Ddb_ddladmin

   Show answer

   Answer: B

   Explanation

   dbmanager: Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

   Source: Adapted from community discussion — OCP

   Next question

9. Question 109 · 354 in bank

   You have an on-premises Microsoft SQL Server 2019 instance named SQL1 that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named MI1 and an Azure Storage account named storage1. You plan to migrate db1 to MI1 by using the backup and restore process. You need to ensure that you can back up db1 to storage1. The solution must meet the following requirements: ✑ Use block blob storage. ✑ Maximize security. What should you do on storage1?

   • AGenerate a shared access signature (SAS).
   • BCreate an access policy.
   • CRotate the storage keys.
   • DEnable infrastructure encryption.

   Show answer

   Answer: A

   Explanation

   To ensure that you can back up db1 to storage1 and meet the requirements, you should generate a shared access signature (SAS) on storage1. A SAS is a URI that grants restricted access rights to storage objects. By using a SAS URI, you can delegate access to resources in your storage account, such as a container or a blob, without exposing your account key. Option B is incorrect because creating an access policy does not provide block blob storage or maximize security. Option C is incorrect because rotating the storage keys does not address the requirements for using block blob storage and maximizing security. Option D is incorrect because enabling infrastructure encryption does not address the requirement for using block blob storage.

   Source: Adapted from community discussion — PMiao

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

10. Question 110 · 354 in bank

    Select 2

    You have an Azure SQL database named DB1. A user named User1 has an Azure Active Directory (Azure AD) account. You need to provide User1 with the ability to add and remove columns from the tables in DB1. The solution must use the principle of least privilege. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    • AAssign the database user the db_owner role.
    • BCreate a contained database user.
    • CCreate a login and an associated database user.
    • DAssign the database user the db_ddladmin role.

    Show answer

    Answer: BD

    Explanation

    D is correct. However, you could also select B or C because User1 has an Azure Active Directory (Azure AD) account. So you could create a contained database user with the Azure AD account, or you could create a Login and User with the Azure AD account. Since principal of least privilege is mentioned, wouldnt a contained Azure AD User be the better option?

    Source: Adapted from community discussion — lukelin08

    References

    • Microsoft Learn

    Next question

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

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