Encryption and Decryption

--CREATE First Database for encryption and decryption testing

USE master;

GO

CREATE DATABASE Encry1

ON

( NAME = Encry1_dat,

FILENAME = 'C:\Encry1Data.mdf',

SIZE = 3,

MAXSIZE = 5,

FILEGROWTH = 1 )

LOG ON

( NAME = Encry1_log,

FILENAME = 'C:\Encry1Log.ldf',

SIZE = 1MB,

MAXSIZE = 5MB,

FILEGROWTH = 1MB ) ;

GO

--create one table which will use encrypted and decrypted data in it

Use Encry1

GO

Create Table emps

(

name varchar(20),

dept varchar(20),

EncryptedName Varbinary(256),

DecryptedName Varchar(20)

)

Insert into emps (Name,Dept)

Select 'Ritesh','MIS' union all

Select 'Rajan','Acct'

union all

Select 'Rashmi','IT'

Select * from emps

go

--create one Database Master Key

Create master key

Encryption by Password ='$qlhub1234'

--Create One Certificate

Create Certificate AdvCert

With Subject= 'Certificate to encrypt emps table',

Start_date = '2009-09-29',

Expiry_date ='2012-02-07'

GO

--create symmetric key

Create Symmetric Key AdvSym

With Algorithm =AES_256

Encryption by Certificate AdvCert

GO

--open symmetric key

Open Symmetric key AdvSym

Decryption by certificate AdvCert

GO

--check encryption function whether it is working or not

Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName from emps

go

--there is null in EncryptedName column right now,

--we are updating EncryptedName column with encrypted data

update emps set EncryptedName=EncryptByKey(Key_Guid(N'AdvSym'),Name)

GO

--look at the status of data now.

select * from emps

GO

--let us check whether decryption is working or not

Select Name,Dept,EncryptedName,

Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps

go

--update emps table DecryptedName column with Decrypted data

Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))

GO

--since we want to backup DMK and want to restore it in

--different server, I am removing encryption by SMK

--as SMK is instance based, it may be possible that

--you are trying to restore database in different instance

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

--again opening master key before taking up backup

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'

--backing up master key to physical file

BACKUP MASTER KEY TO FILE = 'C:\Encry1.DMK'

ENCRYPTION BY PASSWORD='$qlhub1234'

GO

--now backing up database Encry1

BACKUP DATABASE Encry1

TO DISK = 'C:\Encry1.bak'

GO

Now are done with Encry1 database and going to create Encry2 database and will perform certain operation there.

--create another database in same instance

--if you wish, you can do it in other server

--or may be in different instance too.

USE master;

GO

CREATE DATABASE Encry2

ON

( NAME = Encry2_dat,

FILENAME = 'D:\Encry2Data.mdf',

SIZE = 3,

MAXSIZE = 5,

FILEGROWTH = 1 )

LOG ON

( NAME = Encry2_log,

FILENAME = 'D:\Encry2Log.ldf',

SIZE = 1MB,

MAXSIZE = 5MB,

FILEGROWTH = 1MB ) ;

GO

--restore our Encry1 database to Encry2

USE master;

GO

RESTORE DATABASE Encry2

from disk = 'C:\Encry1.bak'

WITH REPLACE,

MOVE 'Encry1_dat' TO

'D:\Encry2Data.mdf',

MOVE 'Encry1_log'

TO 'D:\Encry2Log.ldf'

go

--as soon as you restore database

--look at the status of the data which are encrypted.

--you will not getting anything,even you have certificate

--master key everything is in backup of Encry1

USE Encry2

go

Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,

Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps

go

--you need to open master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'

GO

--put the service level encryption back

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

GO

--restore your Database master key from file

RESTORE MASTER KEY FROM FILE = 'C:\Encry1.DMK'

DECRYPTION BY PASSWORD = '$qlhub1234'

ENCRYPTION BY PASSWORD='$qlhub1234'

GO

--open symmetric key,

--no need to create it as it was there in backup

Open Symmetric key AdvSym

Decryption by certificate AdvCert

GO

--check the data back, you will get everything as it is.

Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,

Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps

go