Skip to content

lesson-4-restore-database-to-virtual-machine-from-url.md

Latest commit

 

History

History
90 lines (65 loc) · 3.88 KB

lesson-4-restore-database-to-virtual-machine-from-url.md

File metadata and controls

90 lines (65 loc) · 3.88 KB
title Lesson 5. (Optional) Encrypt your database using TDE | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology database-engine
ms.topic conceptual
ms.assetid ba793c8f-665a-4c46-b68d-f558a37906b2
author MikeRayMSFT
ms.author mikeray
manager craigg

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