How should you complete the Transact-SQL statements?

DRAG DROP

You have a database named DB1 in a Microsoft Azure virtual machine (VM). You install a certificate named TDECert on the server.

You must encrypt all data at rest and provide real-time encryption and decryption for transmitted and received data.

You need to implement Transparent Data Encryption for DB1.

How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

View Answer

Answer:

Explanation:

To use TDE, follow these steps.

– Create a master key

– Create or obtain a certificate protected by the master key

– Create a database encryption key and protect it by the certificate

– Set the database to use encryption

Example:

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<UseStrongPasswordHere>’;

GO

CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My Certificate’;

GO

USE AdventureWorks2012;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE AdventureWorks2012

SET ENCRYPTION ON;

GO

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017