Fast Start for Microsoft Azure - SQL Server IaaS Workshop

Section 5 SQL on Azure Security

Instructor Demo Manual

Instructor Edition(Book Title Hidden Style)

Conditions and Terms of Use

The contents of this package are for informational and training purposes only and are provided "as is" without warranty of any kind, whether express or implied, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, and non-infringement.

Training package content, including URLs and other Internet Web site references, is subject to change without notice. Because Microsoft must respond to changing market conditions, the content should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

© 2015Microsoft Corporation. All rights reserved.

Copyright and Trademarks

© 2015Microsoft Corporation. All rights reserved.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

For more information, see Use of Microsoft Copyrighted Content at

Microsoft®, Internet Explorer®, and Windows® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other Microsoft products mentioned herein may be either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.

© 2015 Microsoft Corporation

Transparent Data Encryption1

Contents

Demo 1: Transparent Data Encryption

Exercise 1: Enable TDE

Demo 1: Transparent Data Encryption

Introduction

This Demo provides guidance on how to use Transparent Data Encryption to protect data at rest. TDE will also protect database backup.

Objectives

After completing this Demo, you will be able to:

  • Configure SQL Server TDE
  • Restore TDE enabled database
Scenario

The Demo is to enable TDE for a user database and try to restore the database to an unauthorized SQL Server.

Exercise 1: Enable TDE

Objectives

In this exercise, we will create certificate used to encrypt user database.

Task 1: Enable TDE

Execute following TSQL script to enable TDE on demo database

  1. Login to SQL Server Instance and run SSMS
  2. Copy below demo script to Azure SQL VM and execute it step by step.

--TDE Sample Script

--Create C:\Temp folder if it doesn't exist

USEmaster;

GO

--Step 1: Create Master Key to proect encryption certificate

CREATEMASTERKEYENCRYPTIONBYPASSWORD='P@$$w0rd';

GO

--Step 2: Create Certficiate to encrypt user database

CREATECERTIFICATETDECertWITHSUBJECT='TDECert';

GO

--Step 3: Create User database for testing

CREATEDATABASE TDEDB;

GO

USE TDEDB;

GO

--Step 4: Create Sample table and sample data for this demo

CREATETABLE t1(id INTIDENTITYPRIMARYKEY, name VARCHAR(200));

GO

INSERTINTO t1 VALUES ('abc');

GO 10

--Step 5: Enable TDE

--Below statement will fail due to missing encryption key

ALTERDATABASE TDEDB SETENCRYPTIONON--fail

--Now create database encryption key using previously created certificate

CREATEDATABASEENCRYPTIONKEY WITHALGORITHM=AES_256ENCRYPTIONBYSERVERCERTIFICATETDECert;

--Now try again to enable TDE

ALTERDATABASE TDEDB SETENCRYPTIONON;

--Step 6: Backup database

BACKUPDATABASE TDEDB TODISK='c:\temp\TDEDB.bak'WITHCOMPRESSION

--Step 7: Copy the backup file to a different SQL VM and try to restore it

--This operation will fail due to missing certificate

--Change the destination folder if necessary

RESTOREDATABASE TDEDB FROMDISK='f:\temp\TDEDB.bak'

--Step 8: Take a backup of the the certificate

USEmaster;

BACKUPCERTIFICATETDECertTOFILE='c:\temp\TDEcert.cer'

WITHPRIVATEKEY

(FILE='c:\temp\TDECertPvt.key',

ENCRYPTIONBYPASSWORD='P@$$w0rd1')

GO

--Step 9: Copy the TDEcert.cer, TDECertPvt.Key to the other SQL VM and create the certificate

CREATEMASTERKEYENCRYPTIONBYPASSWORD='P@$$w0rd1';

CREATECERTIFICATETDECertFROMFILE='f:\temp\TDEcert.cer'

WITHPRIVATEKEY (FILE='f:\temp\TDECertPvt.key',DECRYPTIONBYPASSWORD='P@$$w0rd1')

--Step 10: Try restore the database again, replace the folder if necessary

RESTOREDATABASE TDEDB FROMDISK='f:\temp\TDEDB.bak'

WITHMOVE'TDEDB'TO'f:\temp\tde.mdf',

MOVE'TDEDB_log'TO'f:\temp\tde_log.ldf'

--Step 11: Select the test data

SELECT * FROM TDEDB..t1

© 2015 Microsoft Corporation