RSS

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

20 Oct

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

 

 

 

 

 

 

 

 
Leave a comment

Posted by on October 20, 2012 in Encryption Tips

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s