MSSQL: Creating Transparent Data Encryption

What is TDE?

Transparent Data Encryption
To be clear, encryption is not a way to prevent users from accessing an instance. Encryption is a way of obfuscating the database with keys or password.  To control users from accessing the database and the tables is through logins, authentication, and role.
This feature is very similar to Oracle's TDE where the key is stored in "keystore" or Oracle Key Vault while the SQL Server TDE key is stored in Master database associating with a certificate which will be used to encrypt the user database. SQL Server and Oracle offer similar encryption algorithms. Oracle does offer more granular encryption such as Column Encryption and Invisible Column. 

Image taken from https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/media/tde-architecture.gif?view=sql-server-2017

To sum up the SQL Server TDE, it basically can be accomplished in 4 steps.

1. create a master key in the Master database
2. create a certificate in the Master database protected by master Key
3. create encryption key in User database protected by the certificate
4. alter database to enable encryption.

Example

Use Master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ’MyPassword1!’
GO
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ’TDE Certificate’;
GO

-- back up the certificate. This is used for recovery.

BACKUP CERTIFICATE TDE_Certificate TO FILE = ’d:\TDE_Certificate.cer’ WITH PRIVATE KEY (FILE = ’D:\TDE_Certificate.key’, ENCRYPTION BY PASSWORD = ’MyPassword1!’);
GO
Use VCDB
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO
ALTER DATABASE VCDB SET ENCRYPTION ON;
GO
select * from sys.dm_database_encryption_keys;

This is just an example. I am not in a position to recommend the encryption on VCDB database.

-- Before restoring the database.

create certificate TDE_Certificate from file = 'd:\TDE_Certificate.cer' with private key (FILE = 'D:\TDE_Certificate.key', deryption by password = 'MyPassword1';