Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 321:

  DRAG DROP

  You have an instance of SQL Server on Azure Virtual Machines named SQL1. SQL1 contains a database named DB1.

  You need to enable Transparent Data Encryption (TDE) for DB1.

  Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Explanation:

  Step 1: a master key in the master database

  Enabling TDE

  To enable TDE for a user database, we create a Database Master Key (DMK), a Certificate, then a Database Encryption Key and turn on the encryption in the user database. The steps below outlines the steps.

  Create a Database Master Key (DMK)

  The first step is to create a DMK in the master database. The below DDL command (CREATE MASTER KEY) is used for the DMK creation. The password used in the script should be strong as per your password policy and kept safe. It is

  required to recover the user database in another server with the same master database restored.

  USE master;

  GO

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';

  GO

  Step 2: a certificate in the master database

  Create the certificate

  A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. The certificates can be a self signed certificate or externally signed certificate (by a third party Certificate Authority like

  Verisign, Symantec etc). We cannot create an externally signed certificate with SQL Server but is possible to import an existing externally signed certificate.

  The CREATE CERTIFICATE statement can load a certificate from a file, a binary constant, or an assembly. This statement can also generate a key pair and create a self-signed certificate. In this scenario , we are using self signed certificate

  as an external validation is not quite often used while working with SQL Server.

  The below script will create the certificate 'TDETest_Cert' in the master database.

  USE master;

  GO

  CREATE CERTIFICATE TDETest_Certificate WITH SUBJECT = 'TDETest_Cert',

  START_DATE = '2020-02-02',

  EXPIRY_DATE = '2020-12-12';

  GO

  Arguments :

  SUBJECT = Field in the metadata of the certificate as defined in the X.509 standard.

  START_DATE = Date on which the certificate becomes valid.

  EXPIRY_DATE = Date on which the certificate expires.

  Once the script is executed, a certifcate named 'TDETest_Certificate' will be created under the Certificates folder under Security folder in the master database.

  Step 3: a database encryption key in DB1

  Create the Database Encryption Key (DEK)

  The DEK is required to be created in the user database before the database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level,

  without any special code modifications.

  The Certificate created above encrypts the DEK in the user database.

  USE [TDETest]

  GO

  CREATE DATABASE ENCRYPTION KEY

  WITH ALGORITHM = AES_256

  ENCRYPTION BY SERVER CERTIFICATE TDETest_Certificate;

  GO

  Note: Transparent Data Encryption Using Certificates and EKM

  Transparent Data Encryption (TDE) is one of the key security features available in SQL Server from SQL Server 2008 onwards. Using this feature, the ‘data at rest’ in the physical files for the database, are protected from unauthorized access

  if the files are copied, or the physical media is stolen. TDE is available with SQL Server, Azure SQL Database, and Azure Synapse Analytics (SQL DW) data files.

  Reference:

  https://www.sqlservercentral.com/steps/stairway-level-1-transparent-data-encryption-in-sql-server-using-certificates-database-master-key-method-and-ekm-method

• Question 322:

  DRAG DROP

  You need to configure user authentication for the SERVER1 databases. The solution must meet the security and compliance requirements.

  Which three 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.

  Select and Place:

  

  Correct Answer:

  

  Scenario: Authenticate database users by using Active Directory credentials.

  The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1.

  Create and populate Azure AD.

  2.

  Optional: Associate or change the active directory that is currently associated with your Azure Subscription.

  3.

  Create an Azure Active Directory administrator. (Step 1)

  4.

  Configure your client computers.

  5.

  Create contained database users in your database mapped to Azure AD identities. (Step 2)

  6.

  Connect to your database by using Azure AD identities. (Step 3)

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview

• Question 323:

  DRAG DROP

  You need to configure user authentication for the SERVER1 databases. The solution must meet the security and compliance requirements.

  Which three 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.

  Select and Place:

  

  Correct Answer:

  

  Scenario: Authenticate database users by using Active Directory credentials.

  The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1.

  Create and populate Azure AD.

  2.

  Optional: Associate or change the active directory that is currently associated with your Azure Subscription.

  3.

  Create an Azure Active Directory administrator. (Step 1)

  4.

  Configure your client computers.

  5.

  Create contained database users in your database mapped to Azure AD identities. (Step 2)

  6.

  Connect to your database by using Azure AD identities. (Step 3)

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview

• Question 324:

  DRAG DROP

  You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.

  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.

  Select and Place:

  

  Correct Answer:

  

  Automating Azure SQL DB index and statistics maintenance using Azure Automation:

  1.

  Create Azure automation account (Step 1)

  2.

  Import SQLServer module (Step 2)

  3.

  Add Credentials to access SQL DB This will use secure way to hold login name and password that will be used to access Azure SQL DB

  4.

  Add a runbook to run the maintenance (Step 3) Steps:

  1.

  Click on "runbooks" at the left panel and then click "add a runbook"

  2.

  Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"

  

  5. Schedule task (Step 4) Steps:

  1.

  Click on Schedules

  2.

  Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.

  Reference: https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/ba-p/368974

• Question 325:

  DRAG DROP

  You create all of the tables and views for ResearchDB1.

  You need to implement security for ResearchDB1.

  The solution must meet the security and compliance requirements.

  Which three 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.

  Select and Place:

  

  Correct Answer: