Microsoft DP-300日本語 Exam Questions : Administering Relational Databases on Microsoft Azure (DP-300日本語版)

Correct Answer: C  🗳️

Explanation: Only visible for VCEPrep members. You can sign-up / login (it's free).

Correct Answer:

See the explanation part for the complete Solution.
Explanation:
Use SQL Server Management Studio Always Encrypted Wizard and encrypt:
Database: db1
Schema: SalesLT
Table: Customer
Column: LastName
Encryption feature: Always Encrypted
Column master key store: Windows Certificate Store
Do not use Transparent Data Encryption. TDE encrypts the database at rest, but this task specifically requires Always Encrypted, which protects selected columns and keeps the encryption keys outside the database engine. Microsoft states that Always Encrypted uses column encryption keys to encrypt column data and column master keys to protect those column encryption keys. Column master keys are stored outside the database system, such as in the Windows certificate store.
Method 1 - SSMS Always Encrypted Wizard
This is the correct method for the simulation.
Step 1: Open SSMS and connect to Azure SQL Database
Open SQL Server Management Studio.
Connect to the Azure SQL logical server that hosts db1.
Use a SQL admin account or Microsoft Entra admin account.
In Options > Connection Properties, select database:
db1
Connect.
If SSMS cannot connect, go to the Azure portal and add your client IP address under the SQL server firewall
/networking settings.
Step 2: Open the table column
In Object Explorer:
Expand Databases.
Expand db1.
Expand Tables.
Expand:
SalesLT.Customer
Expand Columns.
Locate:
LastName
Microsoft confirms that the Always Encrypted Wizard can be launched at the database, table, or individual column level. For one column, the cleanest path is to launch it directly from the column.
Step 3: Launch the Always Encrypted Wizard
Right-click the LastName column, then select:
Encrypt Column
or, depending on the SSMS version:
Always Encrypted Wizard
Alternative path:
Right-click db1 > Tasks > Always Encrypted Wizard
Then manually select:
SalesLT.Customer.LastName
Step 4: Select the LastName column for encryption
On the Column Selection page:
Find:
SalesLT.Customer.LastName
Select the checkbox for LastName.
Set the encryption type.
Use:
Randomized
unless the lab specifically requires searching or equality filtering on LastName.
Reason: Randomized encryption is stronger because identical plaintext values produce different ciphertext values. Deterministic encryption allows equality lookups, joins, grouping, and indexing, but leaks more pattern information because identical plaintext values produce identical encrypted values. Microsoft describes deterministic encryption as query-friendly but more pattern-revealing, while randomized encryption is more secure but does not support normal searching/grouping/joining without secure enclaves.
For this task, the requirement is only to encrypt the LastName column, so Randomized is the safer default.
Step 5: Choose or create a Column Encryption Key
For Encryption Key, select a new key such as:
CEK_Auto1
or create a new column encryption key if one does not already exist.
This is the key that encrypts the data in the LastName column. Microsoft states that a column encryption key encrypts the data in encrypted columns, and the column master key encrypts/protects the column encryption key.
Select Next.
Step 6: Configure the Column Master Key in Windows Certificate Store
On the key configuration page, create a new Column Master Key.
Use settings like:
Setting
Value
Column master key name
CMK_WindowsCert or default generated name
Key store
Windows Certificate Store
Certificate location
Current User
Certificate
Generate new certificate
Column encryption key
CEK_Auto1 or default generated CEK
In many SSMS versions, the wizard creates both:
CMK_Auto1
CEK_Auto1
That is acceptable as long as the CMK key store is Windows Certificate Store.
Microsoft states that SQL Server Management Studio supports column master keys stored in the Windows Certificate Store, and that a column master key can be a certificate stored in Windows Certificate Store.
Step 7: Run the wizard
On the final wizard pages:
Review the configuration.
Choose:
Proceed to finish now
or:
Run immediately
Select Finish.
SSMS will generate the column master key metadata, column encryption key metadata, and perform the data encryption operation. Microsoft explains that the wizard can encrypt selected columns and can generate a new column master key and column encryption key when needed.
During encryption, SSMS may temporarily create a new table, copy data, encrypt the selected column, and swap the table back, depending on whether secure enclaves are used. Microsoft notes that the wizard can move data out of the database and perform cryptographic operations inside the SSMS process when secure enclave in-place encryption is not used.
Verification Steps
Step 1: Confirm Always Encrypted metadata exists
Run this in db1:
SELECT
name,
key_store_provider_name,
key_path
FROM sys.column_master_keys;
You should see a column master key that uses the Windows certificate store provider.
Then run:
SELECT
name
FROM sys.column_encryption_keys;
You should see the column encryption key created by the wizard.
Step 2: Confirm LastName is encrypted
Run:
SELECT
t.name AS table_name,
c.name AS column_name,
c.encryption_type_desc,
cek.name AS column_encryption_key
FROM sys.columns AS c
JOIN sys.tables AS t
ON c.object_id = t.object_id
LEFT JOIN sys.column_encryption_keys AS cek
ON c.column_encryption_key_id = cek.column_encryption_key_id
WHERE t.name = ' Customer '
AND SCHEMA_NAME(t.schema_id) = ' SalesLT '
AND c.name = ' LastName ' ;
Expected result:
table_name: Customer
column_name: LastName
encryption_type_desc: RANDOMIZED
column_encryption_key: CEK_Auto1 or similar
If you selected deterministic encryption, the expected value will be:
DETERMINISTIC
The key requirement is that encryption_type_desc is no longer NULL.
Step 3: Test viewing the encrypted column
Open a new SSMS query connection without Always Encrypted enabled and run:
SELECT TOP 10 LastName
FROM SalesLT.Customer;
You should not see normal plaintext values.
Then reconnect with Always Encrypted enabled:
In SSMS, select Connect > Database Engine.
Select Options.
Go to Additional Connection Parameters.
Add:
Column Encryption Setting=Enabled
Connect again.
Run:
SELECT TOP 10 LastName
FROM SalesLT.Customer;
A client that has access to the Windows certificate/private key should be able to decrypt the values. Microsoft explains that the database stores encrypted data and key metadata, while client-side components with access to the column master key perform encryption and decryption.
Important Exam Notes
Do not choose Azure Key Vault
The task explicitly says:
You must use the Windows Certificate Store.
So the column master key should not be stored in Azure Key Vault.
Wrong:
Azure Key Vault
Correct:
Windows Certificate Store
Do not use TDE
TDE is not column-level Always Encrypted. It encrypts database files/logs at rest, but users and administrators querying the database still see plaintext if they have SQL permissions.
Correct technology:
Always Encrypted
Correct key store:
Windows Certificate Store
Correct target column:
SalesLT.Customer.LastName

Correct Answer:

See the explanation part for the complete Solution.
Explanation:
Create an Azure Private Endpoint for the Azure SQL logical server that hosts db1, place the private endpoint in VNET1, and integrate it with the private DNS zone:
privatelink.database.windows.net
This is the correct solution because Azure SQL Database is a PaaS service. You do not assign a private IP directly to db1. Instead, Azure creates a private endpoint network interface in the virtual network. That private endpoint receives a private IP address from a subnet in VNET1, and clients in VNET1 use that private IP path to reach the SQL server. Microsoft defines a private endpoint as a network interface that uses a private IP address from your virtual network to connect privately to a Private Link resource such as Azure SQL Database.
Azure Portal Method - Recommended for Simulation
Step 1: Identify the SQL logical server that hosts db1
Sign in to the Azure portal.
Search for SQL databases.
Open db1.
On the database overview page, identify the Server name.
The private endpoint is created for the Azure SQL logical server, not for the database object alone. For Azure SQL Database, the Private Link resource type is:
Microsoft.Sql/servers
and the target subresource is:
sqlServer
Microsoft lists Azure SQL Database private endpoint DNS configuration under Microsoft.Sql/servers with subresource sqlServer.
Step 2: Open the SQL server networking page
Open the Azure SQL logical server that hosts db1.
In the left menu, go to:
Security > Networking
Select the Private access tab.
Select Create a private endpoint.
Microsoft's Azure SQL private endpoint workflow is performed from the SQL server resource under Networking > Private access, where you can create or manage private endpoint connections.
Step 3: Configure the private endpoint basics
On the Create private endpoint page:
Setting
Value
Subscription
Use the lab subscription
Resource group
Use the lab resource group
Name
pe-db1-sql
Region
Same region as VNET1, if possible
The name is not exam-critical. The critical part is that the endpoint is associated with VNET1 and the SQL server that hosts db1.
Step 4: Configure the target resource
On the Resource tab, configure:
Setting
Value
Connection method
Connect to an Azure resource in my directory
Resource type
Microsoft.Sql/servers
Resource
SQL logical server that hosts db1
Target sub-resource
sqlServer
Do not choose storage, VM, managed instance, or any unrelated resource type. This is Azure SQL Database, so the target subresource must be sqlServer.
Step 5: Configure VNET1 and subnet
On the Virtual Network tab:
Setting
Value
Virtual network
VNET1
Subnet
Select an available subnet in VNET1
Private IP configuration
Dynamic is fine unless the lab requires static
Azure will create a network interface for the private endpoint and assign it a private IP address from the selected subnet. Microsoft notes that the network interface page for the private endpoint shows the private IP address assigned to the private endpoint connection.
Step 6: Configure private DNS integration
On the DNS tab:
Enable private DNS zone integration.
Use or create the private DNS zone:
privatelink.database.windows.net
Link the private DNS zone to:
VNET1
This is not optional in a clean exam solution. Without DNS integration, clients may still resolve the SQL server name to the public endpoint instead of the private endpoint. Microsoft states that DNS is critical because it resolves the private endpoint IP address, and for Azure SQL Database the recommended private DNS zone is privatelink.database.windows.net.
Step 7: Review and create
Select Review + create.
Confirm:
Resource: SQL logical server hosting db1
Target subresource: sqlServer
Virtual network: VNET1
Private DNS zone: privatelink.database.windows.net
Select Create.
After deployment, the SQL server will have a private endpoint connection associated with VNET1.
Step 8: Approve the private endpoint connection if required
In most same-directory deployments, approval may be automatic. If approval is pending:
Open the SQL logical server.
Go to:
Networking > Private access
Select the pending private endpoint connection.
Select Approve.
Microsoft documents that SQL administrators can approve or reject private endpoint connections from the SQL server private access page.
Step 9: Optional but recommended - Disable public network access
The task only says you need to connect by private IP from VNET1. It does not explicitly say to block public access. But if the exam expects private-only access, then disable public access after the private endpoint works.
On the SQL logical server:
Go to:
Security > Networking > Public access
Set Public network access to:
Disabled
or select:
Deny public network access
Save.
Be careful: Microsoft states that adding a private endpoint does not automatically block public routing to the logical server. Public access must be denied separately if you want private-only access.
How to Connect from SSMS
You should connect from a machine that is inside VNET1, such as an Azure VM joined to VNET1.
Step 1: Test DNS from a VM in VNET1
From a VM in VNET1, run:
nslookup < sql-server-name > .database.windows.net
Expected result: the name should resolve through the private endpoint path and return a private IP address from VNET1's address space.
Microsoft explains that connection URLs do not change; DNS resolution is overridden so the existing service FQDN resolves to the private endpoint private IP address.
Step 2: Connect with SSMS
In SSMS, connect using the normal Azure SQL server name:
< sql-server-name > .database.windows.net
Then select database:
db1
Use normal SQL authentication or Microsoft Entra authentication.
Do not type the raw private IP address into SSMS unless the lab specifically forces it. For Azure SQL, the correct operational pattern is to connect to the SQL server FQDN and allow private DNS to resolve that FQDN to the private endpoint IP. Direct IP connection can cause TLS/certificate name problems because the server certificate matches the DNS name, not the private IP.
Verification
The task is complete when all of these are true:
Private endpoint exists for the SQL logical server hosting db1.
Target subresource is sqlServer.
The private endpoint is deployed into VNET1.
A private IP address is assigned to the private endpoint NIC.
Private DNS zone privatelink.database.windows.net exists.
The private DNS zone is linked to VNET1.
The SQL server FQDN resolves to the private endpoint private IP from inside VNET1.
SSMS can connect to db1 from a VM or client connected to VNET1.
Final Exam-Lab Action
Use the Azure portal and configure:
SQL server hosting db1
> Networking
> Private access
> Create private endpoint
Resource type: Microsoft.Sql/servers
Target subresource: sqlServer
Virtual network: VNET1
Private DNS zone: privatelink.database.windows.net
Then connect from a VM or client in VNET1 using:
< sql-server-name > .database.windows.net
That is the correct way to ensure db1 is reachable through a private IP address on VNET1.

Correct Answer: D  🗳️

Explanation: Only visible for VCEPrep members. You can sign-up / login (it's free).

Correct Answer:

Explanation:

Box 1: Hash
Scenario:
Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.
A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.
Box 2: Round-robin
Scenario:
You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.
A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:
When you cannot identify a single key to distribute your data.
If your data doesn't frequently join with data from other tables.
When there are no obvious keys to join.
Reference:
https://rajanieshkaushikk.com/2020/09/09/how-to-choose-right-data-distribution-strategy-for-azure-synapse/