HOTSPOT (Drag and Drop is not supported)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 Microsoft Entra 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.Hot Area:
Answer(s): A
Box 1: -AssignIdentityThe New-AzSqlServer cmdlet creates an Azure SQL Database server.Parameter -AssignIdentityGenerate and assign a Microsoft Entra Identity for this server for use with key management services like Azure KeyVault.Box 2: KeyIdParameter -KeyIdThe Azure Key Vault URI that is used for encryption.Example: Create a new Azure SQL Database server with TDE CMKNew-AzSqlServer -ResourceGroupName "ResourceGroup01" -Location "East US" -ServerName "server01" - ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential) -AssignIdentity -IdentityType "UserAssigned" -PrimaryUserAssignedIdentityId "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/ resourceGroups/resourcegroup01/providers/Microsoft.ManagedIdentity/userAssignedIdentities/identity01" - UserAssignedIdentityId "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/ resourcegroup01/providers/Microsoft.ManagedIdentity/userAssignedIdentities/identity01" -KeyId https:// contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901
https://learn.microsoft.com/en-us/powershell/module/az.sql/New-AzSqlServe
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.
Answer(s): B,C
B: You can connect to an Azure SQL server using an Azure Private Endpoint.Azure Private endpoint is the fundamental building block for Private Link in Azure. It enables Azure resources, like virtual machines (VMs), to privately and securely communicate with Private Link resources such as Azure SQL server.C: Azure SQL Database and Azure Synapse IP firewall rulesWhen you create a new server in Azure SQL Database or Azure Synapse Analytics named mysqlserver, for example, a server-level firewall blocks all access to the public endpoint for the server (which is accessible at mysqlserver.database.windows.net).
https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
DRAG DROP (Drag and Drop is not supported)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.Select and Place:
Step 1: Create a Microsoft Entra service principal and grant the service principal permissions for Vault1. Set up SQL Server TDE Extensible Key Management by using Azure Key VaultSet up a Microsoft Entra service principalTo grant your SQL Server instance access permissions to your Azure key vault, you need a service principal account in Microsoft Entra ID.Step 2: On SQL1, create an asymmetric key.Be sure to use an asymmetric RSA 2048 or 3072 key so that it's supported by SQL Server.Step 3: On SQL1, crate a cryptographic provider and Microsoft SQL Server credential.Configure SQL ServerCreate a cryptographic provider by using the SQL Server Connector, which is an EKM provider for the Azure key vault.Set up a SQL Server credential for a SQL Server login to use the key vault.Step 4: On SQL1, create a login from the asymmetric key.Create a new login from the asymmetric key in SQL Server.
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key- management-using-the-azure-key-vault
You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named VM1 and a Microsoft Entra Domain Services 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?
Answer(s): D
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.SQL Server Agent Fixed Database RolesSQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:SQLAgentUserRoleSQLAgentReaderRoleSQLAgentOperatorRoleIncorrect:* SQLAgentUserRoleSQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own.* SQLAgentReaderRoleSQLAgentReaderRole PermissionsSQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.* SQLAgentOperatorRole PermissionsSQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.
https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
DRAG DROP (Drag and Drop is not supported)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:
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';GOStep 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
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.