Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.You have the on-premises networks shown in the following table.You have an Azure subscription that contains an Azure SQL Database server named SQL1. SQL1 contains two databases named DB1 and DB2.You need to configure access to DB1 and DB2. The solution must meet the following requirements:Ensure that DB1 can be accessed only by users in Branch1.Ensure that DB2 can be accessed only by users in Branch2.Solution: You connect to DB1 and run the following command.EXECUTE sp_set_firewall_rule `Deny db1 users', `131.107.11.0', `131.107.11.255'You connect to DB2 and run the following command.EXECUTE sp_set_database_firewall_rule `Deny db2 users', `131.107.10.0', `131.107.10.255'Does this meet the goal?
Answer(s): B
This command attempts to deny access from the opposite branch subnets, but it does not explicitly allow the correct subnets access to the respective databases. Moreover, denying a range is not a typical configuration for SQL Database firewall rules, which are generally based on allowing specific IP ranges.
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.You have the on-premises networks shown in the following table.You have an Azure subscription that contains an Azure SQL Database server named SQL1. SQL1 contains two databases named DB1 and DB2.You need to configure access to DB1 and DB2. The solution must meet the following requirements:Ensure that DB1 can be accessed only by users in Branch1.Ensure that DB2 can be accessed only by users in Branch2.Solution: You connect to the master of SQL1 and run the following command.EXECUTE sp_set_firewall_rule `Allow db1 and db2 users', `131.107.10.0', `131.107.10.255'Does this meet the goal?
Correct:* : You connect to DB1 and run the following command.EXECUTE sp_set_database_firewall_rule `Allow db1 users', `131.107.10.0', `131.107.10.255'You connect to DB2 and run the following command.EXECUTE sp_set_database_firewall_rule `Allow db2 users', `131.107.11.0', `131.107.11.255'Note:Use the sp_set_database_firewall_rule command twice. Once for db1 and once for db2.Incorrect:* You connect to DB1 and run the following command.EXECUTE sp_set_firewall_rule `Allow db1 users', `131.107.10.0', `131.107.10.255' You connect to DB2 and run the following command.EXECUTE sp_set_database_firewall_rule `Allow db2 users', `131.107.11.0', `131.107.11.255'Note:Use sp_set_database_firewall_rule for both databases.* : You connect to DB1 and run the following command.EXECUTE sp_set_firewall_rule `Deny db1 users', `131.107.11.0', `131.107.11.255' You connect to DB2 and run the following command.EXECUTE sp_set_database_firewall_rule `Deny db2 users', `131.107.10.0', `131.107.10.255'--This command attempts to deny access from the opposite branch subnets, but it does not explicitly allow the correct subnets access to the respective databases. Moreover, denying a range is not a typical configuration for SQL Database firewall rules, which are generally based on allowing specific IP ranges.* You connect to the master of SQL1 and run the following command.EXECUTE sp_set_firewall_rule `Allow db1 and db2 users', `131.107.11.0', `131.107.11.255'--This command sets a firewall rule at the server level (master database), allowing both DB1 and DB2 to be accessed from the Branch2 subnet. This does not meet the requirement of restricting DB1 access to Branch1 only.Instead use sp_set_database_firewall_rule, and use it for once for each database.* You connect to the master of SQL1 and run the following command.EXECUTE sp_set_firewall_rule `Allow db1 and db2 users', `131.107.10.0', `131.107.10.255'--Instead use sp_set_database_firewall_rule, and use it for once for each database.Note:sp_set_database_firewall_rule (Azure SQL Database)Creates or updates the database-level firewall rules for your Azure SQL Database. Database firewall rules can be configured for the master database, and for user databases on SQL Database. Database firewall rules can be useful when using contained database users.Syntax sp_set_database_firewall_rule[ @name = ] N'name', [ @start_ip_address = ] 'start_ip_address', [ @end_ip_address = ] 'end_ip_address'[ ; ]sp_set_firewall_rule (Azure SQL Database)Creates or updates the server-level firewall settings for your SQL Database server. This stored procedure is only available in the master database to the server-level principal login or assigned Microsoft Entra ID (formerly Azure Active Directory) principal.Syntax sp_set_firewall_rule[ @name = ] N'name', [ @start_ip_address = ] 'start_ip_address', [ @end_ip_address = ] 'end_ip_address'[ ; ]
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall- rule-azure-sql-database https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure- sql-database
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.You have the on-premises networks shown in the following table.You have an Azure subscription that contains an Azure SQL Database server named SQL1. SQL1 contains two databases named DB1 and DB2.You need to configure access to DB1 and DB2. The solution must meet the following requirements:Ensure that DB1 can be accessed only by users in Branch1.Ensure that DB2 can be accessed only by users in Branch2.Solution: You connect to DB1 and run the following command.EXECUTE sp_set_database_firewall_rule `Allow db1 users', `131.107.10.0', `131.107.10.255'You connect to DB2 and run the following command.EXECUTE sp_set_database_firewall_rule `Allow db2 users', `131.107.11.0', `131.107.11.255'Does this meet the goal?
Answer(s): A
You have an Azure virtual machine that hosts a Microsoft SQL Server 2022 database named DB1.You need to enable Transparent Data Encryption (TDE) for DB1.What should you create first?
Answer(s): D
Step 1: a master key in the master databaseEnabling TDETo 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;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';GONote: Next stepsStep 2: a certificate in the master databaseCreate the certificateA 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;GOCREATE CERTIFICATE TDETest_Certificate WITH SUBJECT = 'TDETest_Cert', START_DATE = '2020-02-02',EXPIRY_DATE = '2020-12-12';GOArguments :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 DB1Create 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]GOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE TDETest_Certificate;GONote: 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.
https://www.sqlservercentral.com/steps/stairway-level-1-transparent-data-encryption-in-sql-server-using- certificates-database-master-key-method-and-ekm-method
SIMULATIONYou need to prevent users from accidentally deleting db1 from the Azure portal.You may need to use SQL Server Management Studio and the Azure portal.
To protect your Azure resources, you can use Resource Locks.To prevent accidental deletion of an Azure database, use the Azure portal to create a "Delete" lock on the database or its parent resources, such as the SQL server or resource group. This lock will prevent any user, including owners, from deleting the database.Step 1: Navigate to the resource: Go to the Azure portal and locate the Azure SQL database you want to protect. You can do this by searching for "SQL databases" or navigating through your resource groups. [Select db1]Step 2: Open the Locks tab: In the database's overview, locate the "Locks" tab under the "Settings" section.Step 3: Add a new lock: Click the "Add" button to create a new lock.Step 4: Configure the lock:Lock Name: Give the lock a descriptive name (e.g., "PreventDeleteLock").Lock Type: Select "Delete".Level: Set the level to "CanNotDelete".Notes (Optional): Add any relevant notes about the lock.Step 5: Apply the lock: Click "OK" to save the lock.
https://techcommunity.microsoft.com/blog/adformysql/preventing-and-recovering-from-accidental-deletion-of- an-azure-database-for-mysq/4388503
Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-300 content, but please register or login to continue.