Backup Database with Encrypted Data and Restore it (Encryption – Decryption in SQL Server 2008 Part 7)

Tuesday, October 6, 2009 |



Well, this is last and final article in the series of Encryption and Decryption in SQL Server 2008. This article will give you all you need about database encryption – decryption, backup and restore of database with encrypted data.


If you are new reader, came directly to this article, I would like you to go through all 6 past articles as it will make your concept clearer. Below given are the links for past articles in this series.



Ok so, we will not talk too much and will directly now drive to practical stuff.  We are going to create one database, create one table in that database, and encrypt data of that table and backup complete database. Once we will have backup of database, we will restore it in different database, encrypted table will not be able to show you data with decryption. We will have to restore Database Master Key and then we will be able to perform any operation on that table.


--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

Hope you have enjoyed this journey with SQLHub.com


Please do leave your comments and feedback about this series; it will encourage me to come up with more interesting series like this.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

4 comments:

Anonymous said...

Riteshbhai, wonderful code, help me lot. thank

-A. Bhuva

Anonymous said...

Just one word “EXCELLENT”. Really one of the good articles for encryption.

--Stef

Anonymous said...

It is the best articles related to encrytion data for all DBA levels. Especially, well documented examples from explaination, creation, use, backup and restore. thank you for sharing your knowledge with public.

Anonymous said...

Just read your article and was wondering if you could assist me with an issue i have o.

I have a 3rd part program apex sql that keeps track of any changes that happen on the database tables.(including my encrypted data)

the values held in the apex table are ntext and the value being stored are similar to this '0x006703876CC9154594CCF905B95230EA010000001A2EA8F7B91E97C7F3BA5F3067ABD818AF91EA7D5CE8704EF8616028B4D6DD80'

Is there any way i can decrpt this???

i have access the keys and certificates etc.

I have tried to decrypt the ntext and tried converting the ntext to varbinary, but with no avail.

Any ideas would be grateful

my email is mark_forrest@hotmail.com