정답:
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
