<

Encryption - Decryption in SQL Server 2008 and backup database with encrypted data and restore it somewhere else.

Published on
27,139 Points
15,939 Views
12 Endorsements
Last Modified:
Awarded
Community Pick
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, we have improved and in-built facilities for Encryption and Decryption within SQL Server.

To encrypt data and decrypt it, it is really a very crucial task as one mistake and your data go out of your reach. It can become more difficult when you will encrypt some data in one database in one server and try to restore that database into different server.

In this Article, I am going to show you the script which can encrypt data in one database on one server, take its backup, and restore that encrypted database anywhere else and you will get your data with 100% security and no data risk.

We will now create one database which is going to be used in throughout this example.

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

Open in new window


As soon as you are ready with database, let us now move on to create one table, we will encrypt data in that table after inserting some records.

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

Open in new window


Ok. Now we have database and table ready to encrypt. Before we really encrypt the data, we should be armed with some weapons. Let us prepare it.

 
--create one Database Master Key
Create master key
Encryption by Password ='$qlhub1234'

Open in new window


To know more about Database Master Key, Please Click Here.

Now, we will need Certificate based on the database master key we have generated above.

 
--Create One Certificate
Create Certificate AdvCert
With Subject= 'Certificate to encrypt emps table',
Start_date = '2009-09-29',
Expiry_date ='2012-02-07'
GO

Open in new window


To know more about Certificate, Please Click Here.

Based on the certificate, we should create Symmetric key which will be used to actually encrypt the data.


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

Open in new window


To know more about Symmetric key, please click here.

Now before we actually update our table with encrypted data, I would prefer to see it with SELECT query. Let us do that.


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

Open in new window



Now, we are fine with encryption, but, if we can’t decrypt it is of no worth. Let us check whether decryption is properly working or not.

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

Open in new window


So finally we achieved encryption and decryption in our test database.

Now, while we can encrypt data and decrypt data, but what happens when it is time to backup database and restore it ? What do you need to do?

For making the same database work anywhere else (including a restore to Live), we are use to backup the database and restore it somewhere. But, with it being encrypted, we will have to give same treatment to database master key as well (backup and restore)

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

Open in new window


Once we took back up of database and master key. If you are having different server, use it or/else create new database and restored above backed up database and key in new database in same server.

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

Open in new window


if you think that you have already restored database which has everything in it (data, sps, views, certificates, master key etc.) and it should directly working as it is then you are wrong. Check it out.

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

Open in new window


So, now what to do in order to get our data back (decrypt it)? Simply follow the steps given below.

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

Open in new window


Now, you are done!!!

Please do leave your comments and feedback about this article; it will encourage me to come up with more interesting articles like this.
--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

Open in new window

12
Author:RiteshShah
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free