HOTSPOT -
You have an Azure subscription that is linked to a hybrid Azure Active Directory (Azure AD) tenant. The subscription contains an Azure Synapse Analytics SQL pool named Pool1.
You need to recommend an authentication solution for Pool1. The solution must support multi-factor authentication (MFA) and database-level authentication.
Which authentication solution or solutions should you include in the recommendation? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Box 1: Azure AD authentication -
Azure Active Directory authentication supports Multi-Factor authentication through Active Directory Universal Authentication.
Box 2: Contained database users -
Azure Active Directory Uses contained database users to authenticate identities at the database level.
Incorrect:
SQL authentication: To connect to dedicated SQL pool (formerly SQL DW), you must provide the following information:
✑ Fully qualified servername
✑ Specify SQL authentication
✑ Username
✑ Password
Default database (optional)
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-authentication
You have a data warehouse in Azure Synapse Analytics.
You need to ensure that the data in the data warehouse is encrypted at rest.
What should you enable?
Answer:
A
Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview
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?
Answer:
A
Logins and users: A user account in a database can be associated with a login that is stored in the master database or can be a user name that is stored in an individual database.
A login is an individual account in the master database, to which a user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login.
A user account is an individual account in any database that may be, but does not have to be, linked to a login. With a user account that is not linked to a login, the credential information is stored with the user account.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage
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?
Answer:
C
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
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:
Answer:
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
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:
Answer:
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/
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:
Answer:
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
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?
Answer:
B
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
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?
Answer:
D
If your database contains sensitive data that is protected by Always Encrypted, migration process using Azure Data Studio with DMS will automatically migrate your Always Encrypted keys to your target SQL Server on Azure Virtual Machine.
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-virtual-machine-online-ads
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.
Answer:
CD
C: Logins and users: A user account in a database can be associated with a login that is stored in the master database or can be a user name that is stored in an individual database.
A login is an individual account in the master database, to which a user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login.
A user account is an individual account in any database that may be, but does not have to be, linked to a login. With a user account that is not linked to a login, the credential information is stored with the user account.
D: db_ddladmin: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command (such as adding and removing columns) in a database.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15
