Transparent data encryption (TDE)2 Created by Eng.Ahmed Abdel Moteleb

Special thanks to the engineer Ahmed Abdel Moteleb

Introduction

Database Encryption this very Important Feature in SQL Server 2008 becouse in 2005 you can make Encryption but not in Whole Database you can make it on Tables but when Comming SQL Server 2008 Come With him this New Feature Database Encryption nooooooooooooooooooooooow You Can Encrypt The Whole Database i write Article in this Subject Since one year (02/11/2011)Check it :

https://mostafaelmasry.wordpress.com/2011/11/02/transparent-data-encryption-tde-2/

Before 2 days my Manager Aske me to make Encryption to Whole Database on one from My Client but the Problem this Client use SQL Server 2005 and i forget it’s Impossible to make Encryption on Whole Database in SQL Server 2005 So i asked my Friend

Eng.Ahmed Abdel Moteleb Database Administrstrator and Developer Team Leader

He told me you can make it on SQL Server 2008 and i try it more than once so try this Code and he Send to me this Script : Lets go to See the Script

1-CREATE MASTER KEY

USE MASTER;

GO

IF EXISTS(SELECT*FROMsys.symmetric_keysWHEREname=N’##MS_DatabaseMasterKey##’)

Begin

DROPMASTERKEY;

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

Print‘Master Key Drop + Created’

END

Else

begin

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

print‘masteer key Created’

end

GO

2-CREATE CERTIFICATE

IF EXISTS(SELECT*FROMsys.certificatesWHEREname=N’MYDATABASECERT’)

Begin

DropCertificateMYDATABASECERT

CREATECERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Else

begin

Create CERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Go

3-Backup certificate

Backup certificateMYDATABASE_CERTtofile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’, encryption bypassword=‘@dmin$afeP@$$w0rd’);

4- Encrypt database with my created Certificate

create databaseencryptionkeywithalgorithm=aes_128

encryption byservercertificateMYDATABASECERT;

alter database{Database Name}setencryptionon;

5- take full backup 4 ur database with over write option

6- Take log backup 4 ur database with appen option

7- delete ur database orders_book and delete ur certificate on root master–>security–>certificate

8- try to restore ur backup normally — u will get error no certificate

Fix the Error

 

go

create certificateMYDATABASE_CERTfromfile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’,

decryption bypassword=‘@dmin$afeP@$$w0rd’);

Now try to restore ur backup again u will successfuly restore

Special thanks to the engineer Ahmed Abdel Moteleb