Lesson 5. (Optional) Encrypt your database using TDE

As an optional step, you can encrypt the newly created database. Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. This kind of encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. For more information, see Transparent Data Encryption (TDE) and Move a TDE Protected Database to Another SQL Server.

This lesson assumes you already completed the following steps:

• You have an Azure Storage account.

• You have created a container under your Azure Storage account.

• You have created a policy on a container with read, write, and list rights. You also generated a SAS key.

• You have created a SQL Server credential on the source machine.

• You have created a database by following the steps that are described in Lesson 4.

If you want to encrypt a database, follow these steps:

1. In the source machine, modify and run the following statements in a query window:

   
   -- Create a master key and a server certificate   
   USE master   
   GO   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MySQLKey01';   
   GO   
   CREATE CERTIFICATE MySQLCert WITH SUBJECT = 'SQL - Azure Storage Certification'   
   GO   
   -- Create a backup of the server certificate in the master database.   
   -- Store TDS certificates in the source machine locally.   
   BACKUP CERTIFICATE MySQLCert   
   TO FILE = 'C:\certs\MySQLCert.CER'   
   WITH PRIVATE KEY   
   (   
   FILE = 'C:\certs\MySQLPrivateKeyFile.PVK',   
   ENCRYPTION BY PASSWORD = 'MySQLKey01'   
   );  
   
   

2. Then, encrypt your new database in the source machine by following these steps:

   
   -- Switch to the new database.   
   -- Create a database encryption key, that is protected by the server certificate in the master database.    
   -- Alter the new database to encrypt the database using TDE.   
   USE TestDB1;   
   GO   
   -- Encrypt your database   
   CREATE DATABASE ENCRYPTION KEY   
   WITH ALGORITHM = AES_256   
   ENCRYPTION BY SERVER CERTIFICATE MySQLCert   
   GO   
   
   ALTER DATABASE TestDB1   
   SET ENCRYPTION ON   
   GO  
   
   

If you want to learn the encryption state of a database and its associated database encryption keys, run the following statement:

  
SELECT * FROM sys.dm_database_encryption_keys;   
GO  
  

For detailed information the Transact-SQL statements that have been used in this lesson, see CREATE DATABASE (SQL Server Transact-SQL), ALTER DATABASE (Transact-SQL), CREATE MASTER KEY (Transact-SQL), CREATE CERTIFICATE (Transact-SQL), and sys.dm_database_encryption_keys (Transact-SQL).

Next Lesson:

Lesson 6: Migrate a database from a source machine on-premises to a destination machine in Azure