--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 'Bhaumik','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
--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
--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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (7)
Commented:
Commented:
Commented:
Commented:
Commented:
View More